<!DOCTYPE html>
<html>
  <head>
  <meta http-equiv="content-type" content="text/html; charset=utf-8">
  <meta content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=0" name="viewport">
  <meta name="description" content="刘清政">
  <meta name="keyword" content="hexo-theme">
  
    <link rel="shortcut icon" href="/css/images/logo.png">
  
  <title>
    
      db/MySQL系列/12-MySQL系列之-MyCat | Justin-刘清政的博客
    
  </title>
  <link href="//cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet">
  <link href="//cdnjs.cloudflare.com/ajax/libs/nprogress/0.2.0/nprogress.min.css" rel="stylesheet">
  <link href="//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/styles/tomorrow.min.css" rel="stylesheet">
  
<link rel="stylesheet" href="/css/style.css">

  
    
<link rel="stylesheet" href="/css/plugins/gitment.css">

  
  <script src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
  <script src="//cdnjs.cloudflare.com/ajax/libs/geopattern/1.2.3/js/geopattern.min.js"></script>
  <script src="//cdnjs.cloudflare.com/ajax/libs/nprogress/0.2.0/nprogress.min.js"></script>
  
    
<script src="/js/qrious.js"></script>

  
  
    
<script src="/js/gitment.js"></script>

  
  

  
<meta name="generator" content="Hexo 4.2.0"></head>
<div class="wechat-share">
  <img src="/css/images/logo.png" />
</div>

  <body>
    <header class="header fixed-header">
  <div class="header-container">
    <a class="home-link" href="/">
      <div class="logo"></div>
      <span>Justin-刘清政的博客</span>
    </a>
    <ul class="right-list">
      
        <li class="list-item">
          
            <a href="/" class="item-link">主页</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/tags/" class="item-link">标签</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/archives/" class="item-link">归档</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/about/" class="item-link">关于我</a>
          
        </li>
      
    </ul>
    <div class="menu">
      <span class="icon-bar"></span>
      <span class="icon-bar"></span>
      <span class="icon-bar"></span>
    </div>
    <div class="menu-mask">
      <ul class="menu-list">
        
          <li class="menu-item">
            
              <a href="/" class="menu-link">主页</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/tags/" class="menu-link">标签</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/archives/" class="menu-link">归档</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/about/" class="menu-link">关于我</a>
            
          </li>
        
      </ul>
    </div>
  </div>
</header>

    <div id="article-banner">
  <h2>db/MySQL系列/12-MySQL系列之-MyCat</h2>



  <p class="post-date">2019-12-24</p>
    <!-- 不蒜子统计 -->
    <span id="busuanzi_container_page_pv" style='display:none' class="">
        <i class="icon-smile icon"></i> 阅读数：<span id="busuanzi_value_page_pv"></span>次
    </span>
  <div class="arrow-down">
    <a href="javascript:;"></a>
  </div>
</div>
<main class="app-body flex-box">
  <!-- Article START -->
  <article class="post-article">
    <section class="markdown-content"><h1 id="1-MyCAT基础架构图"><a href="#1-MyCAT基础架构图" class="headerlink" title="1. MyCAT基础架构图"></a>1. MyCAT基础架构图</h1><p><img src="https:////upload-images.jianshu.io/upload_images/16956686-7e5ff50e4071c7eb.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/843/format/webp" alt="img"></p>
<p>image.png</p>
<h1 id="2-MyCAT基础架构准备"><a href="#2-MyCAT基础架构准备" class="headerlink" title="2. MyCAT基础架构准备"></a>2. MyCAT基础架构准备</h1><h2 id="2-1-环境准备："><a href="#2-1-环境准备：" class="headerlink" title="2.1 环境准备："></a>2.1 环境准备：</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">两台虚拟机 db01 db02</span><br><span class="line">每台创建四个mysql实例：3307 3308 3309 3310</span><br></pre></td></tr></table></figure>

<h2 id="2-2-删除历史环境："><a href="#2-2-删除历史环境：" class="headerlink" title="2.2 删除历史环境："></a>2.2 删除历史环境：</h2><figure class="highlight kotlin"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">pkill mysqld</span><br><span class="line">rm -rf /<span class="keyword">data</span>/<span class="number">330</span>* </span><br><span class="line">mv /etc/my.cnf /etc/my.cnf.bak</span><br></pre></td></tr></table></figure>

<h2 id="2-3-创建相关目录初始化数据"><a href="#2-3-创建相关目录初始化数据" class="headerlink" title="2.3 创建相关目录初始化数据"></a>2.3 创建相关目录初始化数据</h2><figure class="highlight kotlin"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">mkdir /<span class="keyword">data</span>/<span class="number">33</span>&#123;<span class="number">07</span>..<span class="number">10</span>&#125;/<span class="keyword">data</span> -p</span><br><span class="line">mysqld --initialize-insecure  --user=mysql --datadir=/<span class="keyword">data</span>/<span class="number">3307</span>/<span class="keyword">data</span> --basedir=/app/mysql</span><br><span class="line">mysqld --initialize-insecure  --user=mysql --datadir=/<span class="keyword">data</span>/<span class="number">3308</span>/<span class="keyword">data</span> --basedir=/app/mysql</span><br><span class="line">mysqld --initialize-insecure  --user=mysql --datadir=/<span class="keyword">data</span>/<span class="number">3309</span>/<span class="keyword">data</span> --basedir=/app/mysql</span><br><span class="line">mysqld --initialize-insecure  --user=mysql --datadir=/<span class="keyword">data</span>/<span class="number">3310</span>/<span class="keyword">data</span> --basedir=/app/mysql</span><br></pre></td></tr></table></figure>

<h2 id="2-4-准备配置文件和启动脚本"><a href="#2-4-准备配置文件和启动脚本" class="headerlink" title="2.4 准备配置文件和启动脚本"></a>2.4 准备配置文件和启动脚本</h2><figure class="highlight jsx"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br><span class="line">116</span><br><span class="line">117</span><br><span class="line">118</span><br><span class="line">119</span><br><span class="line">120</span><br><span class="line">121</span><br><span class="line">122</span><br><span class="line">123</span><br><span class="line">124</span><br><span class="line">125</span><br><span class="line">126</span><br><span class="line">127</span><br><span class="line">128</span><br><span class="line">129</span><br><span class="line">130</span><br><span class="line">131</span><br><span class="line">132</span><br><span class="line">133</span><br><span class="line">134</span><br><span class="line">135</span><br><span class="line">136</span><br><span class="line">137</span><br><span class="line">138</span><br><span class="line">139</span><br><span class="line">140</span><br><span class="line">141</span><br><span class="line">142</span><br><span class="line">143</span><br><span class="line">144</span><br><span class="line">145</span><br><span class="line">146</span><br><span class="line">147</span><br><span class="line">148</span><br><span class="line">149</span><br><span class="line">150</span><br><span class="line">151</span><br><span class="line">152</span><br><span class="line">153</span><br><span class="line">154</span><br><span class="line">155</span><br><span class="line">156</span><br><span class="line">157</span><br><span class="line">158</span><br><span class="line">159</span><br><span class="line">160</span><br><span class="line">161</span><br><span class="line">162</span><br><span class="line">163</span><br><span class="line">164</span><br><span class="line">165</span><br><span class="line">166</span><br><span class="line">167</span><br><span class="line">168</span><br><span class="line">169</span><br><span class="line">170</span><br><span class="line">171</span><br><span class="line">172</span><br><span class="line">173</span><br><span class="line">174</span><br><span class="line">175</span><br><span class="line">176</span><br><span class="line">177</span><br><span class="line">178</span><br><span class="line">179</span><br><span class="line">180</span><br><span class="line">181</span><br><span class="line">182</span><br><span class="line">183</span><br><span class="line">184</span><br><span class="line">185</span><br><span class="line">186</span><br><span class="line">187</span><br><span class="line">188</span><br><span class="line">189</span><br><span class="line">190</span><br><span class="line">191</span><br><span class="line">192</span><br><span class="line">193</span><br><span class="line">194</span><br><span class="line">195</span><br><span class="line">196</span><br><span class="line">197</span><br><span class="line">198</span><br><span class="line">199</span><br><span class="line">200</span><br><span class="line">201</span><br><span class="line">202</span><br><span class="line">203</span><br><span class="line">204</span><br><span class="line">205</span><br><span class="line">206</span><br><span class="line">207</span><br><span class="line">208</span><br><span class="line">209</span><br><span class="line">210</span><br><span class="line">211</span><br><span class="line">212</span><br><span class="line">213</span><br><span class="line">214</span><br><span class="line">215</span><br><span class="line">216</span><br><span class="line">217</span><br><span class="line">218</span><br><span class="line">219</span><br><span class="line">220</span><br><span class="line">221</span><br><span class="line">222</span><br><span class="line">223</span><br><span class="line">224</span><br><span class="line">225</span><br><span class="line">226</span><br><span class="line">227</span><br><span class="line">228</span><br><span class="line">229</span><br><span class="line">230</span><br><span class="line">231</span><br><span class="line">232</span><br><span class="line">233</span><br><span class="line">234</span><br><span class="line">235</span><br><span class="line">236</span><br><span class="line">237</span><br><span class="line">238</span><br><span class="line">239</span><br><span class="line">240</span><br><span class="line">241</span><br><span class="line">242</span><br><span class="line">243</span><br><span class="line">244</span><br><span class="line">245</span><br><span class="line">246</span><br><span class="line">247</span><br><span class="line">248</span><br><span class="line">249</span><br><span class="line">250</span><br><span class="line">251</span><br><span class="line">252</span><br><span class="line">253</span><br></pre></td><td class="code"><pre><span class="line">========db01==============</span><br><span class="line">cat &gt;<span class="regexp">/data/</span><span class="number">3307</span>/my.cnf&lt;&lt;EOF</span><br><span class="line">[mysqld]</span><br><span class="line">basedir=<span class="regexp">/app/my</span>sql</span><br><span class="line">datadir=<span class="regexp">/data/</span><span class="number">3307</span>/data</span><br><span class="line">socket=<span class="regexp">/data/</span><span class="number">3307</span>/mysql.sock</span><br><span class="line">port=<span class="number">3307</span></span><br><span class="line">log-error=<span class="regexp">/data/</span><span class="number">3307</span>/mysql.log</span><br><span class="line">log_bin=<span class="regexp">/data/</span><span class="number">3307</span>/mysql-bin</span><br><span class="line">binlog_format=row</span><br><span class="line">skip-name-resolve</span><br><span class="line">server-id=<span class="number">7</span></span><br><span class="line">gtid-mode=on</span><br><span class="line">enforce-gtid-consistency=<span class="literal">true</span></span><br><span class="line">log-slave-updates=<span class="number">1</span></span><br><span class="line">EOF</span><br><span class="line"></span><br><span class="line">cat &gt;<span class="regexp">/data/</span><span class="number">3308</span>/my.cnf&lt;&lt;EOF</span><br><span class="line">[mysqld]</span><br><span class="line">basedir=<span class="regexp">/app/my</span>sql</span><br><span class="line">datadir=<span class="regexp">/data/</span><span class="number">3308</span>/data</span><br><span class="line">port=<span class="number">3308</span></span><br><span class="line">socket=<span class="regexp">/data/</span><span class="number">3308</span>/mysql.sock</span><br><span class="line">log-error=<span class="regexp">/data/</span><span class="number">3308</span>/mysql.log</span><br><span class="line">log_bin=<span class="regexp">/data/</span><span class="number">3308</span>/mysql-bin</span><br><span class="line">binlog_format=row</span><br><span class="line">skip-name-resolve</span><br><span class="line">server-id=<span class="number">8</span></span><br><span class="line">gtid-mode=on</span><br><span class="line">enforce-gtid-consistency=<span class="literal">true</span></span><br><span class="line">log-slave-updates=<span class="number">1</span></span><br><span class="line">EOF</span><br><span class="line"></span><br><span class="line">cat &gt;<span class="regexp">/data/</span><span class="number">3309</span>/my.cnf&lt;&lt;EOF</span><br><span class="line">[mysqld]</span><br><span class="line">basedir=<span class="regexp">/app/my</span>sql</span><br><span class="line">datadir=<span class="regexp">/data/</span><span class="number">3309</span>/data</span><br><span class="line">socket=<span class="regexp">/data/</span><span class="number">3309</span>/mysql.sock</span><br><span class="line">port=<span class="number">3309</span></span><br><span class="line">log-error=<span class="regexp">/data/</span><span class="number">3309</span>/mysql.log</span><br><span class="line">log_bin=<span class="regexp">/data/</span><span class="number">3309</span>/mysql-bin</span><br><span class="line">binlog_format=row</span><br><span class="line">skip-name-resolve</span><br><span class="line">server-id=<span class="number">9</span></span><br><span class="line">gtid-mode=on</span><br><span class="line">enforce-gtid-consistency=<span class="literal">true</span></span><br><span class="line">log-slave-updates=<span class="number">1</span></span><br><span class="line">EOF</span><br><span class="line">cat &gt;<span class="regexp">/data/</span><span class="number">3310</span>/my.cnf&lt;&lt;EOF</span><br><span class="line">[mysqld]</span><br><span class="line">basedir=<span class="regexp">/app/my</span>sql</span><br><span class="line">datadir=<span class="regexp">/data/</span><span class="number">3310</span>/data</span><br><span class="line">socket=<span class="regexp">/data/</span><span class="number">3310</span>/mysql.sock</span><br><span class="line">port=<span class="number">3310</span></span><br><span class="line">log-error=<span class="regexp">/data/</span><span class="number">3310</span>/mysql.log</span><br><span class="line">log_bin=<span class="regexp">/data/</span><span class="number">3310</span>/mysql-bin</span><br><span class="line">binlog_format=row</span><br><span class="line">skip-name-resolve</span><br><span class="line">server-id=<span class="number">10</span></span><br><span class="line">gtid-mode=on</span><br><span class="line">enforce-gtid-consistency=<span class="literal">true</span></span><br><span class="line">log-slave-updates=<span class="number">1</span></span><br><span class="line">EOF</span><br><span class="line"></span><br><span class="line">cat &gt;<span class="regexp">/etc/</span>systemd/system/mysqld3307.service&lt;&lt;EOF</span><br><span class="line">[Unit]</span><br><span class="line">Description=MySQL Server</span><br><span class="line">Documentation=man:mysqld(<span class="number">8</span>)</span><br><span class="line">Documentation=http:<span class="comment">//dev.mysql.com/doc/refman/en/using-systemd.html</span></span><br><span class="line">After=network.target</span><br><span class="line">After=syslog.target</span><br><span class="line">[Install]</span><br><span class="line">WantedBy=multi-user.target</span><br><span class="line">[Service]</span><br><span class="line">User=mysql</span><br><span class="line">Group=mysql</span><br><span class="line">ExecStart=<span class="regexp">/app/my</span>sql/bin/mysqld --defaults-file=<span class="regexp">/data/</span><span class="number">3307</span>/my.cnf</span><br><span class="line">LimitNOFILE = <span class="number">5000</span></span><br><span class="line">EOF</span><br><span class="line"></span><br><span class="line">cat &gt;<span class="regexp">/etc/</span>systemd/system/mysqld3308.service&lt;&lt;EOF</span><br><span class="line">[Unit]</span><br><span class="line">Description=MySQL Server</span><br><span class="line">Documentation=man:mysqld(<span class="number">8</span>)</span><br><span class="line">Documentation=http:<span class="comment">//dev.mysql.com/doc/refman/en/using-systemd.html</span></span><br><span class="line">After=network.target</span><br><span class="line">After=syslog.target</span><br><span class="line">[Install]</span><br><span class="line">WantedBy=multi-user.target</span><br><span class="line">[Service]</span><br><span class="line">User=mysql</span><br><span class="line">Group=mysql</span><br><span class="line">ExecStart=<span class="regexp">/app/my</span>sql/bin/mysqld --defaults-file=<span class="regexp">/data/</span><span class="number">3308</span>/my.cnf</span><br><span class="line">LimitNOFILE = <span class="number">5000</span></span><br><span class="line">EOF</span><br><span class="line"></span><br><span class="line">cat &gt;<span class="regexp">/etc/</span>systemd/system/mysqld3309.service&lt;&lt;EOF</span><br><span class="line">[Unit]</span><br><span class="line">Description=MySQL Server</span><br><span class="line">Documentation=man:mysqld(<span class="number">8</span>)</span><br><span class="line">Documentation=http:<span class="comment">//dev.mysql.com/doc/refman/en/using-systemd.html</span></span><br><span class="line">After=network.target</span><br><span class="line">After=syslog.target</span><br><span class="line">[Install]</span><br><span class="line">WantedBy=multi-user.target</span><br><span class="line">[Service]</span><br><span class="line">User=mysql</span><br><span class="line">Group=mysql</span><br><span class="line">ExecStart=<span class="regexp">/app/my</span>sql/bin/mysqld --defaults-file=<span class="regexp">/data/</span><span class="number">3309</span>/my.cnf</span><br><span class="line">LimitNOFILE = <span class="number">5000</span></span><br><span class="line">EOF</span><br><span class="line">cat &gt;<span class="regexp">/etc/</span>systemd/system/mysqld3310.service&lt;&lt;EOF</span><br><span class="line">[Unit]</span><br><span class="line">Description=MySQL Server</span><br><span class="line">Documentation=man:mysqld(<span class="number">8</span>)</span><br><span class="line">Documentation=http:<span class="comment">//dev.mysql.com/doc/refman/en/using-systemd.html</span></span><br><span class="line">After=network.target</span><br><span class="line">After=syslog.target</span><br><span class="line"></span><br><span class="line">[Install]</span><br><span class="line">WantedBy=multi-user.target</span><br><span class="line">[Service]</span><br><span class="line">User=mysql</span><br><span class="line">Group=mysql</span><br><span class="line">ExecStart=<span class="regexp">/app/my</span>sql/bin/mysqld --defaults-file=<span class="regexp">/data/</span><span class="number">3310</span>/my.cnf</span><br><span class="line">LimitNOFILE = <span class="number">5000</span></span><br><span class="line">EOF</span><br><span class="line">========db02===============</span><br><span class="line">cat &gt;<span class="regexp">/data/</span><span class="number">3307</span>/my.cnf&lt;&lt;EOF</span><br><span class="line">[mysqld]</span><br><span class="line">basedir=<span class="regexp">/app/my</span>sql</span><br><span class="line">datadir=<span class="regexp">/data/</span><span class="number">3307</span>/data</span><br><span class="line">socket=<span class="regexp">/data/</span><span class="number">3307</span>/mysql.sock</span><br><span class="line">port=<span class="number">3307</span></span><br><span class="line">log-error=<span class="regexp">/data/</span><span class="number">3307</span>/mysql.log</span><br><span class="line">log_bin=<span class="regexp">/data/</span><span class="number">3307</span>/mysql-bin</span><br><span class="line">binlog_format=row</span><br><span class="line">skip-name-resolve</span><br><span class="line">server-id=<span class="number">17</span></span><br><span class="line">gtid-mode=on</span><br><span class="line">enforce-gtid-consistency=<span class="literal">true</span></span><br><span class="line">log-slave-updates=<span class="number">1</span></span><br><span class="line">EOF</span><br><span class="line">cat &gt;<span class="regexp">/data/</span><span class="number">3308</span>/my.cnf&lt;&lt;EOF</span><br><span class="line">[mysqld]</span><br><span class="line">basedir=<span class="regexp">/app/my</span>sql</span><br><span class="line">datadir=<span class="regexp">/data/</span><span class="number">3308</span>/data</span><br><span class="line">port=<span class="number">3308</span></span><br><span class="line">socket=<span class="regexp">/data/</span><span class="number">3308</span>/mysql.sock</span><br><span class="line">log-error=<span class="regexp">/data/</span><span class="number">3308</span>/mysql.log</span><br><span class="line">log_bin=<span class="regexp">/data/</span><span class="number">3308</span>/mysql-bin</span><br><span class="line">binlog_format=row</span><br><span class="line">skip-name-resolve</span><br><span class="line">server-id=<span class="number">18</span></span><br><span class="line">gtid-mode=on</span><br><span class="line">enforce-gtid-consistency=<span class="literal">true</span></span><br><span class="line">log-slave-updates=<span class="number">1</span></span><br><span class="line">EOF</span><br><span class="line">cat &gt;<span class="regexp">/data/</span><span class="number">3309</span>/my.cnf&lt;&lt;EOF</span><br><span class="line">[mysqld]</span><br><span class="line">basedir=<span class="regexp">/app/my</span>sql</span><br><span class="line">datadir=<span class="regexp">/data/</span><span class="number">3309</span>/data</span><br><span class="line">socket=<span class="regexp">/data/</span><span class="number">3309</span>/mysql.sock</span><br><span class="line">port=<span class="number">3309</span></span><br><span class="line">log-error=<span class="regexp">/data/</span><span class="number">3309</span>/mysql.log</span><br><span class="line">log_bin=<span class="regexp">/data/</span><span class="number">3309</span>/mysql-bin</span><br><span class="line">binlog_format=row</span><br><span class="line">skip-name-resolve</span><br><span class="line">server-id=<span class="number">19</span></span><br><span class="line">gtid-mode=on</span><br><span class="line">enforce-gtid-consistency=<span class="literal">true</span></span><br><span class="line">log-slave-updates=<span class="number">1</span></span><br><span class="line">EOF</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">cat &gt;<span class="regexp">/data/</span><span class="number">3310</span>/my.cnf&lt;&lt;EOF</span><br><span class="line">[mysqld]</span><br><span class="line">basedir=<span class="regexp">/app/my</span>sql</span><br><span class="line">datadir=<span class="regexp">/data/</span><span class="number">3310</span>/data</span><br><span class="line">socket=<span class="regexp">/data/</span><span class="number">3310</span>/mysql.sock</span><br><span class="line">port=<span class="number">3310</span></span><br><span class="line">log-error=<span class="regexp">/data/</span><span class="number">3310</span>/mysql.log</span><br><span class="line">log_bin=<span class="regexp">/data/</span><span class="number">3310</span>/mysql-bin</span><br><span class="line">binlog_format=row</span><br><span class="line">skip-name-resolve</span><br><span class="line">server-id=<span class="number">20</span></span><br><span class="line">gtid-mode=on</span><br><span class="line">enforce-gtid-consistency=<span class="literal">true</span></span><br><span class="line">log-slave-updates=<span class="number">1</span></span><br><span class="line">EOF</span><br><span class="line"></span><br><span class="line">cat &gt;<span class="regexp">/etc/</span>systemd/system/mysqld3307.service&lt;&lt;EOF</span><br><span class="line">[Unit]</span><br><span class="line">Description=MySQL Server</span><br><span class="line">Documentation=man:mysqld(<span class="number">8</span>)</span><br><span class="line">Documentation=http:<span class="comment">//dev.mysql.com/doc/refman/en/using-systemd.html</span></span><br><span class="line">After=network.target</span><br><span class="line">After=syslog.target</span><br><span class="line">[Install]</span><br><span class="line">WantedBy=multi-user.target</span><br><span class="line">[Service]</span><br><span class="line">User=mysql</span><br><span class="line">Group=mysql</span><br><span class="line">ExecStart=<span class="regexp">/app/my</span>sql/bin/mysqld --defaults-file=<span class="regexp">/data/</span><span class="number">3307</span>/my.cnf</span><br><span class="line">LimitNOFILE = <span class="number">5000</span></span><br><span class="line">EOF</span><br><span class="line"></span><br><span class="line">cat &gt;<span class="regexp">/etc/</span>systemd/system/mysqld3308.service&lt;&lt;EOF</span><br><span class="line">[Unit]</span><br><span class="line">Description=MySQL Server</span><br><span class="line">Documentation=man:mysqld(<span class="number">8</span>)</span><br><span class="line">Documentation=http:<span class="comment">//dev.mysql.com/doc/refman/en/using-systemd.html</span></span><br><span class="line">After=network.target</span><br><span class="line">After=syslog.target</span><br><span class="line">[Install]</span><br><span class="line">WantedBy=multi-user.target</span><br><span class="line">[Service]</span><br><span class="line">User=mysql</span><br><span class="line">Group=mysql</span><br><span class="line">ExecStart=<span class="regexp">/app/my</span>sql/bin/mysqld --defaults-file=<span class="regexp">/data/</span><span class="number">3308</span>/my.cnf</span><br><span class="line">LimitNOFILE = <span class="number">5000</span></span><br><span class="line">EOF</span><br><span class="line"></span><br><span class="line">cat &gt;<span class="regexp">/etc/</span>systemd/system/mysqld3309.service&lt;&lt;EOF</span><br><span class="line">[Unit]</span><br><span class="line">Description=MySQL Server</span><br><span class="line">Documentation=man:mysqld(<span class="number">8</span>)</span><br><span class="line">Documentation=http:<span class="comment">//dev.mysql.com/doc/refman/en/using-systemd.html</span></span><br><span class="line">After=network.target</span><br><span class="line">After=syslog.target</span><br><span class="line">[Install]</span><br><span class="line">WantedBy=multi-user.target</span><br><span class="line">[Service]</span><br><span class="line">User=mysql</span><br><span class="line">Group=mysql</span><br><span class="line">ExecStart=<span class="regexp">/app/my</span>sql/bin/mysqld --defaults-file=<span class="regexp">/data/</span><span class="number">3309</span>/my.cnf</span><br><span class="line">LimitNOFILE = <span class="number">5000</span></span><br><span class="line">EOF</span><br><span class="line">cat &gt;<span class="regexp">/etc/</span>systemd/system/mysqld3310.service&lt;&lt;EOF</span><br><span class="line">[Unit]</span><br><span class="line">Description=MySQL Server</span><br><span class="line">Documentation=man:mysqld(<span class="number">8</span>)</span><br><span class="line">Documentation=http:<span class="comment">//dev.mysql.com/doc/refman/en/using-systemd.html</span></span><br><span class="line">After=network.target</span><br><span class="line">After=syslog.target</span><br><span class="line">[Install]</span><br><span class="line">WantedBy=multi-user.target</span><br><span class="line">[Service]</span><br><span class="line">User=mysql</span><br><span class="line">Group=mysql</span><br><span class="line">ExecStart=<span class="regexp">/app/my</span>sql/bin/mysqld --defaults-file=<span class="regexp">/data/</span><span class="number">3310</span>/my.cnf</span><br><span class="line">LimitNOFILE = <span class="number">5000</span></span><br><span class="line">EOF</span><br></pre></td></tr></table></figure>

<h2 id="2-5-修改权限，启动多实例"><a href="#2-5-修改权限，启动多实例" class="headerlink" title="2.5 修改权限，启动多实例"></a>2.5 修改权限，启动多实例</h2><figure class="highlight kotlin"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">chown -R mysql.mysql /<span class="keyword">data</span><span class="comment">/*</span></span><br><span class="line"><span class="comment">systemctl start mysqld3307</span></span><br><span class="line"><span class="comment">systemctl start mysqld3308</span></span><br><span class="line"><span class="comment">systemctl start mysqld3309</span></span><br><span class="line"><span class="comment">systemctl start mysqld3310</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"</span></span><br><span class="line"><span class="comment">mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"</span></span><br><span class="line"><span class="comment">mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"</span></span><br><span class="line"><span class="comment">mysql -S /data/3310/mysql.sock -e "show variables like 'server_id'"</span></span><br></pre></td></tr></table></figure>

<h2 id="2-6-节点主从规划"><a href="#2-6-节点主从规划" class="headerlink" title="2.6 节点主从规划"></a>2.6 节点主从规划</h2><figure class="highlight css"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">箭头指向谁是主库</span><br><span class="line">    10<span class="selector-class">.0</span><span class="selector-class">.0</span><span class="selector-class">.51</span><span class="selector-pseudo">:3307</span>    &lt;<span class="selector-tag">-----</span>&gt;  10<span class="selector-class">.0</span><span class="selector-class">.0</span><span class="selector-class">.52</span><span class="selector-pseudo">:3307</span></span><br><span class="line">    10<span class="selector-class">.0</span><span class="selector-class">.0</span><span class="selector-class">.51</span><span class="selector-pseudo">:3309</span>    <span class="selector-tag">------</span>&gt;  10<span class="selector-class">.0</span><span class="selector-class">.0</span><span class="selector-class">.51</span><span class="selector-pseudo">:3307</span></span><br><span class="line">    10<span class="selector-class">.0</span><span class="selector-class">.0</span><span class="selector-class">.52</span><span class="selector-pseudo">:3309</span>    <span class="selector-tag">------</span>&gt;  10<span class="selector-class">.0</span><span class="selector-class">.0</span><span class="selector-class">.52</span><span class="selector-pseudo">:3307</span></span><br><span class="line"></span><br><span class="line">    10<span class="selector-class">.0</span><span class="selector-class">.0</span><span class="selector-class">.52</span><span class="selector-pseudo">:3308</span>  &lt;<span class="selector-tag">-----</span>&gt;    10<span class="selector-class">.0</span><span class="selector-class">.0</span><span class="selector-class">.51</span><span class="selector-pseudo">:3308</span></span><br><span class="line">    10<span class="selector-class">.0</span><span class="selector-class">.0</span><span class="selector-class">.52</span><span class="selector-pseudo">:3310</span>  <span class="selector-tag">-----</span>&gt;     10<span class="selector-class">.0</span><span class="selector-class">.0</span><span class="selector-class">.52</span><span class="selector-pseudo">:3308</span></span><br><span class="line">    10<span class="selector-class">.0</span><span class="selector-class">.0</span><span class="selector-class">.51</span><span class="selector-pseudo">:3310</span>  <span class="selector-tag">-----</span>&gt;     10<span class="selector-class">.0</span><span class="selector-class">.0</span><span class="selector-class">.51</span><span class="selector-pseudo">:3308</span></span><br></pre></td></tr></table></figure>

<h2 id="2-7-分片规划"><a href="#2-7-分片规划" class="headerlink" title="2.7 分片规划"></a>2.7 分片规划</h2><figure class="highlight css"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="selector-tag">shard1</span>：</span><br><span class="line">    <span class="selector-tag">Master</span>：10<span class="selector-class">.0</span><span class="selector-class">.0</span><span class="selector-class">.51</span><span class="selector-pseudo">:3307</span></span><br><span class="line">    <span class="selector-tag">slave1</span>：10<span class="selector-class">.0</span><span class="selector-class">.0</span><span class="selector-class">.51</span><span class="selector-pseudo">:3309</span></span><br><span class="line">    <span class="selector-tag">Standby</span> <span class="selector-tag">Master</span>：10<span class="selector-class">.0</span><span class="selector-class">.0</span><span class="selector-class">.52</span><span class="selector-pseudo">:3307</span></span><br><span class="line">    <span class="selector-tag">slave2</span>：10<span class="selector-class">.0</span><span class="selector-class">.0</span><span class="selector-class">.52</span><span class="selector-pseudo">:3309</span></span><br><span class="line"><span class="selector-tag">shard2</span>：</span><br><span class="line">    <span class="selector-tag">Master</span>：10<span class="selector-class">.0</span><span class="selector-class">.0</span><span class="selector-class">.52</span><span class="selector-pseudo">:3308</span></span><br><span class="line">    <span class="selector-tag">slave1</span>：10<span class="selector-class">.0</span><span class="selector-class">.0</span><span class="selector-class">.52</span><span class="selector-pseudo">:3310</span></span><br><span class="line">    <span class="selector-tag">Standby</span> <span class="selector-tag">Master</span>：10<span class="selector-class">.0</span><span class="selector-class">.0</span><span class="selector-class">.51</span><span class="selector-pseudo">:3308</span></span><br><span class="line">    <span class="selector-tag">slave2</span>：10<span class="selector-class">.0</span><span class="selector-class">.0</span><span class="selector-class">.51</span><span class="selector-pseudo">:3310</span></span><br></pre></td></tr></table></figure>

<h2 id="2-8-开始配置"><a href="#2-8-开始配置" class="headerlink" title="2.8 开始配置"></a>2.8 开始配置</h2><h3 id="shard1"><a href="#shard1" class="headerlink" title="shard1"></a>shard1</h3><h3 id="10-0-0-51-3307-lt-—–-gt-10-0-0-52-3307"><a href="#10-0-0-51-3307-lt-—–-gt-10-0-0-52-3307" class="headerlink" title="10.0.0.51:3307    &lt;—–&gt;  10.0.0.52:3307"></a>10.0.0.51:3307    &lt;—–&gt;  10.0.0.52:3307</h3><h3 id="db02"><a href="#db02" class="headerlink" title="db02"></a>db02</h3><figure class="highlight kotlin"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3307</span>/mysql.sock -e <span class="string">"grant replication slave on *.* to repl@'10.0.0.%' identified by '123';"</span></span><br><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3307</span>/mysql.sock -e <span class="string">"grant all  on *.* to root@'10.0.0.%' identified by '123'  with grant option;"</span></span><br></pre></td></tr></table></figure>

<h3 id="db01"><a href="#db01" class="headerlink" title="db01"></a>db01</h3><figure class="highlight kotlin"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3307</span>/mysql.sock -e <span class="string">"CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"</span></span><br><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3307</span>/mysql.sock -e <span class="string">"start slave;"</span></span><br><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3307</span>/mysql.sock -e <span class="string">"show slave status\G"</span></span><br></pre></td></tr></table></figure>

<h3 id="db02-1"><a href="#db02-1" class="headerlink" title="db02"></a>db02</h3><figure class="highlight kotlin"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3307</span>/mysql.sock -e <span class="string">"CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"</span></span><br><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3307</span>/mysql.sock -e <span class="string">"start slave;"</span></span><br><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3307</span>/mysql.sock -e <span class="string">"show slave status\G"</span></span><br></pre></td></tr></table></figure>

<h3 id="10-0-0-51-3309-——-gt-10-0-0-51-3307"><a href="#10-0-0-51-3309-——-gt-10-0-0-51-3307" class="headerlink" title="10.0.0.51:3309    ——&gt;  10.0.0.51:3307"></a>10.0.0.51:3309    ——&gt;  10.0.0.51:3307</h3><h3 id="db01-1"><a href="#db01-1" class="headerlink" title="db01"></a>db01</h3><figure class="highlight kotlin"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3309</span>/mysql.sock  -e <span class="string">"CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"</span></span><br><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3309</span>/mysql.sock  -e <span class="string">"start slave;"</span></span><br><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3309</span>/mysql.sock  -e <span class="string">"show slave status\G"</span></span><br></pre></td></tr></table></figure>

<h3 id="10-0-0-52-3309-——-gt-10-0-0-52-3307"><a href="#10-0-0-52-3309-——-gt-10-0-0-52-3307" class="headerlink" title="10.0.0.52:3309    ——&gt;  10.0.0.52:3307"></a>10.0.0.52:3309    ——&gt;  10.0.0.52:3307</h3><h3 id="db02-2"><a href="#db02-2" class="headerlink" title="db02"></a>db02</h3><figure class="highlight kotlin"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3309</span>/mysql.sock -e <span class="string">"CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"</span></span><br><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3309</span>/mysql.sock -e <span class="string">"start slave;"</span></span><br><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3309</span>/mysql.sock -e <span class="string">"show slave status\G"</span></span><br></pre></td></tr></table></figure>

<h3 id="shard2"><a href="#shard2" class="headerlink" title="shard2"></a>shard2</h3><h3 id="10-0-0-52-3308-lt-—–-gt-10-0-0-51-3308"><a href="#10-0-0-52-3308-lt-—–-gt-10-0-0-51-3308" class="headerlink" title="10.0.0.52:3308  &lt;—–&gt;    10.0.0.51:3308"></a>10.0.0.52:3308  &lt;—–&gt;    10.0.0.51:3308</h3><h3 id="db01-2"><a href="#db01-2" class="headerlink" title="db01"></a>db01</h3><figure class="highlight kotlin"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3308</span>/mysql.sock -e <span class="string">"grant replication slave on *.* to repl@'10.0.0.%' identified by '123';"</span></span><br><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3308</span>/mysql.sock -e <span class="string">"grant all  on *.* to root@'10.0.0.%' identified by '123'  with grant option;"</span></span><br></pre></td></tr></table></figure>

<h3 id="db02-3"><a href="#db02-3" class="headerlink" title="db02"></a>db02</h3><figure class="highlight kotlin"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3308</span>/mysql.sock -e <span class="string">"CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"</span></span><br><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3308</span>/mysql.sock -e <span class="string">"start slave;"</span></span><br><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3308</span>/mysql.sock -e <span class="string">"show slave status\G"</span></span><br></pre></td></tr></table></figure>

<h3 id="db01-3"><a href="#db01-3" class="headerlink" title="db01"></a>db01</h3><figure class="highlight kotlin"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3308</span>/mysql.sock -e <span class="string">"CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"</span></span><br><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3308</span>/mysql.sock -e <span class="string">"start slave;"</span></span><br><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3308</span>/mysql.sock -e <span class="string">"show slave status\G"</span></span><br></pre></td></tr></table></figure>

<h3 id="10-0-0-52-3310-—–-gt-10-0-0-52-3308"><a href="#10-0-0-52-3310-—–-gt-10-0-0-52-3308" class="headerlink" title="10.0.0.52:3310    —–&gt;       10.0.0.52:3308"></a>10.0.0.52:3310    —–&gt;       10.0.0.52:3308</h3><h3 id="db02-4"><a href="#db02-4" class="headerlink" title="db02"></a>db02</h3><figure class="highlight kotlin"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3310</span>/mysql.sock -e <span class="string">"CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"</span></span><br><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3310</span>/mysql.sock -e <span class="string">"start slave;"</span></span><br><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3310</span>/mysql.sock -e <span class="string">"show slave status\G"</span></span><br></pre></td></tr></table></figure>

<h3 id="10-0-0-51-3310-—–-gt-10-0-0-51-3308"><a href="#10-0-0-51-3310-—–-gt-10-0-0-51-3308" class="headerlink" title="10.0.0.51:3310  —–&gt;     10.0.0.51:3308"></a>10.0.0.51:3310  —–&gt;     10.0.0.51:3308</h3><h3 id="db01-4"><a href="#db01-4" class="headerlink" title="db01"></a>db01</h3><figure class="highlight kotlin"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3310</span>/mysql.sock -e <span class="string">"CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"</span></span><br><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3310</span>/mysql.sock -e <span class="string">"start slave;"</span></span><br><span class="line">mysql  -S /<span class="keyword">data</span>/<span class="number">3310</span>/mysql.sock -e <span class="string">"show slave status\G"</span></span><br></pre></td></tr></table></figure>

<h2 id="2-9-检测主从状态"><a href="#2-9-检测主从状态" class="headerlink" title="2.9 检测主从状态"></a>2.9 检测主从状态</h2><p>mysql -S /data/3307/mysql.sock -e “show slave status\G”|grep Yes<br> mysql -S /data/3308/mysql.sock -e “show slave status\G”|grep Yes<br> mysql -S /data/3309/mysql.sock -e “show slave status\G”|grep Yes<br> mysql -S /data/3310/mysql.sock -e “show slave status\G”|grep Yes<br> 注：如果中间出现错误，在每个节点进行执行以下命令<br> mysql -S /data/3307/mysql.sock -e “stop slave; reset slave all;”<br> mysql -S /data/3308/mysql.sock -e “stop slave; reset slave all;”<br> mysql -S /data/3309/mysql.sock -e “stop slave; reset slave all;”<br> mysql -S /data/3310/mysql.sock -e “stop slave; reset slave all;”</p>
<h2 id="2-10-MySQL分布式架构介绍"><a href="#2-10-MySQL分布式架构介绍" class="headerlink" title="2.10 MySQL分布式架构介绍"></a>2.10 MySQL分布式架构介绍</h2><p><img src="https:////upload-images.jianshu.io/upload_images/16956686-7c753fb3640bc0ec.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/475/format/webp" alt="img"></p>
<p>image.png</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">1. schema拆分及业务分库</span><br><span class="line">2. 垂直拆分-分库分表</span><br><span class="line">3. 水平拆分-分片</span><br></pre></td></tr></table></figure>

<h2 id="2-11-企业代表产品"><a href="#2-11-企业代表产品" class="headerlink" title="2.11 企业代表产品"></a>2.11 企业代表产品</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">360 Atlas-Sharding</span><br><span class="line">Alibaba  cobar </span><br><span class="line">Mycat</span><br><span class="line">TDDL</span><br><span class="line">Heisenberg</span><br><span class="line">Oceanus</span><br><span class="line">Vitess</span><br><span class="line">OneProxy </span><br><span class="line">DRDS</span><br></pre></td></tr></table></figure>

<h1 id="3-MyCAT安装"><a href="#3-MyCAT安装" class="headerlink" title="3. MyCAT安装"></a>3. MyCAT安装</h1><h2 id="3-1-预先安装Java运行环境"><a href="#3-1-预先安装Java运行环境" class="headerlink" title="3.1 预先安装Java运行环境"></a>3.1 预先安装Java运行环境</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">yum install -y java</span><br></pre></td></tr></table></figure>

<h2 id="3-2下载"><a href="#3-2下载" class="headerlink" title="3.2下载"></a>3.2下载</h2><figure class="highlight cpp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">Mycat-server-xxxxx.linux.tar.gz</span><br><span class="line">http:<span class="comment">//dl.mycat.io/</span></span><br></pre></td></tr></table></figure>

<h2 id="3-3-解压文件"><a href="#3-3-解压文件" class="headerlink" title="3.3 解压文件"></a>3.3 解压文件</h2><figure class="highlight css"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="selector-tag">tar</span> <span class="selector-tag">xf</span> <span class="selector-tag">Mycat-server-1</span><span class="selector-class">.6</span><span class="selector-class">.5-release-20180122220033-linux</span><span class="selector-class">.tar</span><span class="selector-class">.gz</span></span><br></pre></td></tr></table></figure>

<h2 id="3-4-软件目录结构"><a href="#3-4-软件目录结构" class="headerlink" title="3.4 软件目录结构"></a>3.4 软件目录结构</h2><figure class="highlight css"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="selector-tag">ls</span></span><br><span class="line"><span class="selector-tag">bin</span>  <span class="selector-tag">catlet</span>  <span class="selector-tag">conf</span>  <span class="selector-tag">lib</span>  <span class="selector-tag">logs</span>  <span class="selector-tag">version</span><span class="selector-class">.txt</span></span><br></pre></td></tr></table></figure>

<h2 id="3-5-启动和连接"><a href="#3-5-启动和连接" class="headerlink" title="3.5 启动和连接"></a>3.5 启动和连接</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">配置环境变量</span><br><span class="line">vim /etc/profile</span><br><span class="line"><span class="built_in">export</span> PATH=/application/mycat/bin:<span class="variable">$PATH</span></span><br><span class="line"><span class="built_in">source</span> /etc/profile</span><br><span class="line">启动</span><br><span class="line">mycat start</span><br><span class="line">连接mycat：</span><br><span class="line">mysql -uroot -p123456 -h 127.0.0.1 -P8066</span><br></pre></td></tr></table></figure>

<h1 id="4-配置文件介绍"><a href="#4-配置文件介绍" class="headerlink" title="4. 配置文件介绍"></a>4. 配置文件介绍</h1><figure class="highlight css"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="selector-tag">logs</span>目录:</span><br><span class="line"><span class="selector-tag">wrapper</span><span class="selector-class">.log</span>       <span class="selector-tag">----</span>&gt;<span class="selector-tag">mycat</span>启动日志</span><br><span class="line"><span class="selector-tag">mycat</span><span class="selector-class">.log</span>         <span class="selector-tag">----</span>&gt;<span class="selector-tag">mycat</span>详细工作日志</span><br><span class="line"><span class="selector-tag">conf</span>目录:</span><br><span class="line"><span class="selector-tag">schema</span><span class="selector-class">.xml</span>      </span><br><span class="line">主配置文件（读写分离、高可用、分布式策略定制、节点控制）</span><br><span class="line"><span class="selector-tag">server</span><span class="selector-class">.xml</span></span><br><span class="line"><span class="selector-tag">mycat</span>软件本身相关的配置</span><br><span class="line"><span class="selector-tag">rule</span><span class="selector-class">.xml</span> </span><br><span class="line">分片规则配置文件,记录分片规则列表、使用方法等</span><br></pre></td></tr></table></figure>

<h1 id="5-应用前环境准备"><a href="#5-应用前环境准备" class="headerlink" title="5.应用前环境准备"></a>5.应用前环境准备</h1><h2 id="5-1-用户创建及数据库导入"><a href="#5-1-用户创建及数据库导入" class="headerlink" title="5.1 用户创建及数据库导入"></a>5.1 用户创建及数据库导入</h2><figure class="highlight kotlin"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">db01:</span><br><span class="line">mysql -S /<span class="keyword">data</span>/<span class="number">3307</span>/mysql.sock </span><br><span class="line">grant all on *.* to <span class="symbol">root@</span><span class="string">'10.0.0.%'</span> identified <span class="keyword">by</span> <span class="string">'123'</span>;</span><br><span class="line">source /root/world.sql</span><br><span class="line"></span><br><span class="line">mysql -S /<span class="keyword">data</span>/<span class="number">3308</span>/mysql.sock </span><br><span class="line">grant all on *.* to <span class="symbol">root@</span><span class="string">'10.0.0.%'</span> identified <span class="keyword">by</span> <span class="string">'123'</span>;</span><br><span class="line">source /root/world.sql</span><br></pre></td></tr></table></figure>

<h2 id="5-2-配置文件处理"><a href="#5-2-配置文件处理" class="headerlink" title="5.2 配置文件处理"></a>5.2 配置文件处理</h2><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">cd /application/mycat/conf</span><br><span class="line">mv schema.xml schema.xml.bak</span><br><span class="line">vim schema.xml </span><br><span class="line"><span class="meta">&lt;?xml version="1.0"?&gt;</span>  </span><br><span class="line"><span class="meta">&lt;!DOCTYPE <span class="meta-keyword">mycat</span>:schema <span class="meta-keyword">SYSTEM</span> <span class="meta-string">"schema.dtd"</span>&gt;</span>  </span><br><span class="line"><span class="tag">&lt;<span class="name">mycat:schema</span> <span class="attr">xmlns:mycat</span>=<span class="string">"http://io.mycat/"</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;<span class="name">schema</span> <span class="attr">name</span>=<span class="string">"TESTDB"</span> <span class="attr">checkSQLschema</span>=<span class="string">"false"</span> <span class="attr">sqlMaxLimit</span>=<span class="string">"100"</span> <span class="attr">dataNode</span>=<span class="string">"dn1"</span>&gt;</span> </span><br><span class="line"><span class="tag">&lt;/<span class="name">schema</span>&gt;</span>  </span><br><span class="line">    <span class="tag">&lt;<span class="name">dataNode</span> <span class="attr">name</span>=<span class="string">"dn1"</span> <span class="attr">dataHost</span>=<span class="string">"localhost1"</span> <span class="attr">database</span>= <span class="string">"wordpress"</span> /&gt;</span>  </span><br><span class="line">    <span class="tag">&lt;<span class="name">dataHost</span> <span class="attr">name</span>=<span class="string">"localhost1"</span> <span class="attr">maxCon</span>=<span class="string">"1000"</span> <span class="attr">minCon</span>=<span class="string">"10"</span> <span class="attr">balance</span>=<span class="string">"1"</span>  <span class="attr">writeType</span>=<span class="string">"0"</span> <span class="attr">dbType</span>=<span class="string">"mysql"</span>  <span class="attr">dbDriver</span>=<span class="string">"native"</span> <span class="attr">switchType</span>=<span class="string">"1"</span>&gt;</span> </span><br><span class="line">        <span class="tag">&lt;<span class="name">heartbeat</span>&gt;</span>select user()<span class="tag">&lt;/<span class="name">heartbeat</span>&gt;</span>  </span><br><span class="line">    <span class="tag">&lt;<span class="name">writeHost</span> <span class="attr">host</span>=<span class="string">"db1"</span> <span class="attr">url</span>=<span class="string">"10.0.0.51:3307"</span> <span class="attr">user</span>=<span class="string">"root"</span> <span class="attr">password</span>=<span class="string">"123"</span>&gt;</span> </span><br><span class="line">            <span class="tag">&lt;<span class="name">readHost</span> <span class="attr">host</span>=<span class="string">"db2"</span> <span class="attr">url</span>=<span class="string">"10.0.0.51:3309"</span> <span class="attr">user</span>=<span class="string">"root"</span> <span class="attr">password</span>=<span class="string">"123"</span> /&gt;</span> </span><br><span class="line">    <span class="tag">&lt;/<span class="name">writeHost</span>&gt;</span> </span><br><span class="line">    <span class="tag">&lt;/<span class="name">dataHost</span>&gt;</span>  </span><br><span class="line"><span class="tag">&lt;/<span class="name">mycat:schema</span>&gt;</span></span><br></pre></td></tr></table></figure>

<h1 id="6-配置文件简单介绍"><a href="#6-配置文件简单介绍" class="headerlink" title="6. 配置文件简单介绍"></a>6. 配置文件简单介绍</h1><h2 id="6-1-逻辑库：schema"><a href="#6-1-逻辑库：schema" class="headerlink" title="6.1 逻辑库：schema"></a>6.1 逻辑库：schema</h2><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="tag">&lt;<span class="name">schema</span> <span class="attr">name</span>=<span class="string">"TESTDB"</span> <span class="attr">checkSQLschema</span>=<span class="string">"false"</span> <span class="attr">sqlMaxLimit</span>=<span class="string">"100"</span> <span class="attr">dataNode</span>=<span class="string">"dn1"</span>&gt;</span> </span><br><span class="line"><span class="tag">&lt;/<span class="name">schema</span>&gt;</span></span><br></pre></td></tr></table></figure>

<h2 id="6-2-数据节点-datanode"><a href="#6-2-数据节点-datanode" class="headerlink" title="6.2 数据节点:datanode"></a>6.2 数据节点:datanode</h2><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="tag">&lt;<span class="name">dataNode</span> <span class="attr">name</span>=<span class="string">"dn1"</span> <span class="attr">dataHost</span>=<span class="string">"localhost1"</span> <span class="attr">database</span>= <span class="string">"world"</span> /&gt;</span></span><br></pre></td></tr></table></figure>

<h2 id="6-3-数据主机：datahost-w和r"><a href="#6-3-数据主机：datahost-w和r" class="headerlink" title="6.3 数据主机：datahost(w和r)"></a>6.3 数据主机：datahost(w和r)</h2><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="tag">&lt;<span class="name">dataHost</span> <span class="attr">name</span>=<span class="string">"localhost1"</span> <span class="attr">maxCon</span>=<span class="string">"1000"</span> <span class="attr">minCon</span>=<span class="string">"10"</span> <span class="attr">balance</span>=<span class="string">"1"</span>  <span class="attr">writeType</span>=<span class="string">"0"</span> <span class="attr">dbType</span>=<span class="string">"mysql"</span>  <span class="attr">dbDriver</span>=<span class="string">"native"</span> <span class="attr">switchType</span>=<span class="string">"1"</span>&gt;</span> </span><br><span class="line">        <span class="tag">&lt;<span class="name">heartbeat</span>&gt;</span>select user()<span class="tag">&lt;/<span class="name">heartbeat</span>&gt;</span>  </span><br><span class="line">    <span class="tag">&lt;<span class="name">writeHost</span> <span class="attr">host</span>=<span class="string">"db1"</span> <span class="attr">url</span>=<span class="string">"10.0.0.51:3307"</span> <span class="attr">user</span>=<span class="string">"root"</span> <span class="attr">password</span>=<span class="string">"123"</span>&gt;</span> </span><br><span class="line">            <span class="tag">&lt;<span class="name">readHost</span> <span class="attr">host</span>=<span class="string">"db2"</span> <span class="attr">url</span>=<span class="string">"10.0.0.52:3309"</span> <span class="attr">user</span>=<span class="string">"root"</span> <span class="attr">password</span>=<span class="string">"123"</span> /&gt;</span> </span><br><span class="line">    <span class="tag">&lt;/<span class="name">writeHost</span>&gt;</span> </span><br><span class="line">    <span class="tag">&lt;/<span class="name">dataHost</span>&gt;</span></span><br></pre></td></tr></table></figure>

<h1 id="7-读写分离结构配置"><a href="#7-读写分离结构配置" class="headerlink" title="7. 读写分离结构配置"></a>7. 读写分离结构配置</h1><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br></pre></td><td class="code"><pre><span class="line">vim schema.xml </span><br><span class="line"></span><br><span class="line"><span class="meta">&lt;?xml version="1.0"?&gt;</span></span><br><span class="line"><span class="meta">&lt;!DOCTYPE <span class="meta-keyword">mycat</span>:schema <span class="meta-keyword">SYSTEM</span> <span class="meta-string">"schema.dtd"</span>&gt;</span>  </span><br><span class="line"><span class="tag">&lt;<span class="name">mycat:schema</span> <span class="attr">xmlns:mycat</span>=<span class="string">"http://io.mycat/"</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;<span class="name">schema</span> <span class="attr">name</span>=<span class="string">"TESTDB"</span> <span class="attr">checkSQLschema</span>=<span class="string">"false"</span> <span class="attr">sqlMaxLimit</span>=<span class="string">"100"</span> <span class="attr">dataNode</span>=<span class="string">"sh1"</span>&gt;</span> </span><br><span class="line"><span class="tag">&lt;/<span class="name">schema</span>&gt;</span>  </span><br><span class="line">        <span class="tag">&lt;<span class="name">dataNode</span> <span class="attr">name</span>=<span class="string">"sh1"</span> <span class="attr">dataHost</span>=<span class="string">"oldguo1"</span> <span class="attr">database</span>= <span class="string">"world"</span> /&gt;</span>         </span><br><span class="line">        <span class="tag">&lt;<span class="name">dataHost</span> <span class="attr">name</span>=<span class="string">"oldguo1"</span> <span class="attr">maxCon</span>=<span class="string">"1000"</span> <span class="attr">minCon</span>=<span class="string">"10"</span> <span class="attr">balance</span>=<span class="string">"1"</span>  <span class="attr">writeType</span>=<span class="string">"0"</span> <span class="attr">dbType</span>=<span class="string">"mysql"</span>  <span class="attr">dbDriver</span>=<span class="string">"native"</span> <span class="attr">switchType</span>=<span class="string">"1"</span>&gt;</span>    </span><br><span class="line">                <span class="tag">&lt;<span class="name">heartbeat</span>&gt;</span>select user()<span class="tag">&lt;/<span class="name">heartbeat</span>&gt;</span>  </span><br><span class="line">        <span class="tag">&lt;<span class="name">writeHost</span> <span class="attr">host</span>=<span class="string">"db1"</span> <span class="attr">url</span>=<span class="string">"10.0.0.51:3307"</span> <span class="attr">user</span>=<span class="string">"root"</span> <span class="attr">password</span>=<span class="string">"123"</span>&gt;</span> </span><br><span class="line">                        <span class="tag">&lt;<span class="name">readHost</span> <span class="attr">host</span>=<span class="string">"db2"</span> <span class="attr">url</span>=<span class="string">"10.0.0.51:3309"</span> <span class="attr">user</span>=<span class="string">"root"</span> <span class="attr">password</span>=<span class="string">"123"</span> /&gt;</span> </span><br><span class="line">        <span class="tag">&lt;/<span class="name">writeHost</span>&gt;</span> </span><br><span class="line">        <span class="tag">&lt;/<span class="name">dataHost</span>&gt;</span>  </span><br><span class="line"><span class="tag">&lt;/<span class="name">mycat:schema</span>&gt;</span></span><br><span class="line"></span><br><span class="line">重启mycat</span><br><span class="line">mycat restart</span><br><span class="line"></span><br><span class="line">读写分离测试</span><br><span class="line"> mysql -uroot -p -h 127.0.0.1 -P8066</span><br><span class="line"> show variables like 'server_id';</span><br><span class="line"> begin;</span><br><span class="line"> show variables like 'server_id';</span><br><span class="line"></span><br><span class="line">总结： </span><br><span class="line">以上案例实现了1主1从的读写分离功能，写操作落到主库，读操作落到从库.如果主库宕机，从库不能在继续提供服务了。</span><br></pre></td></tr></table></figure>

<h1 id="8-配置读写分离及高可用"><a href="#8-配置读写分离及高可用" class="headerlink" title="8. 配置读写分离及高可用"></a>8. 配置读写分离及高可用</h1><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br></pre></td><td class="code"><pre><span class="line">[root@db01 conf]# mv schema.xml schema.xml.rw</span><br><span class="line">[root@db01 conf]# vim schema.xml</span><br><span class="line"></span><br><span class="line"><span class="meta">&lt;?xml version="1.0"?&gt;</span>  </span><br><span class="line"><span class="meta">&lt;!DOCTYPE <span class="meta-keyword">mycat</span>:schema <span class="meta-keyword">SYSTEM</span> <span class="meta-string">"schema.dtd"</span>&gt;</span>  </span><br><span class="line"><span class="tag">&lt;<span class="name">mycat:schema</span> <span class="attr">xmlns:mycat</span>=<span class="string">"http://io.mycat/"</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;<span class="name">schema</span> <span class="attr">name</span>=<span class="string">"TESTDB"</span> <span class="attr">checkSQLschema</span>=<span class="string">"false"</span> <span class="attr">sqlMaxLimit</span>=<span class="string">"100"</span> <span class="attr">dataNode</span>=<span class="string">"sh1"</span>&gt;</span> </span><br><span class="line"><span class="tag">&lt;/<span class="name">schema</span>&gt;</span>  </span><br><span class="line">    <span class="tag">&lt;<span class="name">dataNode</span> <span class="attr">name</span>=<span class="string">"sh1"</span> <span class="attr">dataHost</span>=<span class="string">"oldguo1"</span> <span class="attr">database</span>= <span class="string">"world"</span> /&gt;</span>  </span><br><span class="line">    <span class="tag">&lt;<span class="name">dataHost</span> <span class="attr">name</span>=<span class="string">"oldguo1"</span> <span class="attr">maxCon</span>=<span class="string">"1000"</span> <span class="attr">minCon</span>=<span class="string">"10"</span> <span class="attr">balance</span>=<span class="string">"1"</span>  <span class="attr">writeType</span>=<span class="string">"0"</span> <span class="attr">dbType</span>=<span class="string">"mysql"</span>  <span class="attr">dbDriver</span>=<span class="string">"native"</span> <span class="attr">switchType</span>=<span class="string">"1"</span>&gt;</span> </span><br><span class="line">        <span class="tag">&lt;<span class="name">heartbeat</span>&gt;</span>select user()<span class="tag">&lt;/<span class="name">heartbeat</span>&gt;</span>  </span><br><span class="line">    <span class="tag">&lt;<span class="name">writeHost</span> <span class="attr">host</span>=<span class="string">"db1"</span> <span class="attr">url</span>=<span class="string">"10.0.0.51:3307"</span> <span class="attr">user</span>=<span class="string">"root"</span> <span class="attr">password</span>=<span class="string">"123"</span>&gt;</span> </span><br><span class="line">            <span class="tag">&lt;<span class="name">readHost</span> <span class="attr">host</span>=<span class="string">"db2"</span> <span class="attr">url</span>=<span class="string">"10.0.0.51:3309"</span> <span class="attr">user</span>=<span class="string">"root"</span> <span class="attr">password</span>=<span class="string">"123"</span> /&gt;</span> </span><br><span class="line">    <span class="tag">&lt;/<span class="name">writeHost</span>&gt;</span> </span><br><span class="line">    <span class="tag">&lt;<span class="name">writeHost</span> <span class="attr">host</span>=<span class="string">"db3"</span> <span class="attr">url</span>=<span class="string">"10.0.0.52:3307"</span> <span class="attr">user</span>=<span class="string">"root"</span> <span class="attr">password</span>=<span class="string">"123"</span>&gt;</span> </span><br><span class="line">            <span class="tag">&lt;<span class="name">readHost</span> <span class="attr">host</span>=<span class="string">"db4"</span> <span class="attr">url</span>=<span class="string">"10.0.0.52:3309"</span> <span class="attr">user</span>=<span class="string">"root"</span> <span class="attr">password</span>=<span class="string">"123"</span> /&gt;</span> </span><br><span class="line">    <span class="tag">&lt;/<span class="name">writeHost</span>&gt;</span>        </span><br><span class="line">    <span class="tag">&lt;/<span class="name">dataHost</span>&gt;</span>  </span><br><span class="line"><span class="tag">&lt;/<span class="name">mycat:schema</span>&gt;</span></span><br><span class="line"></span><br><span class="line">真正的 writehost：负责写操作的writehost  </span><br><span class="line">standby  writeHost  ：和readhost一样，只提供读服务</span><br><span class="line"></span><br><span class="line">当写节点宕机后，后面跟的readhost也不提供服务，这时候standby的writehost就提供写服务，</span><br><span class="line">后面跟的readhost提供读服务</span><br><span class="line"></span><br><span class="line">测试：</span><br><span class="line">mysql -uroot -p123456 -h 127.0.0.1 -P 8066</span><br><span class="line">show variables like 'server_id';</span><br><span class="line">读写分离测试</span><br><span class="line"> mysql -uroot -p -h 127.0.0.1 -P8066</span><br><span class="line"> show variables like 'server_id';</span><br><span class="line"> show variables like 'server_id';</span><br><span class="line"> show variables like 'server_id';</span><br><span class="line"> begin;</span><br><span class="line"> show variables like 'server_id';</span><br><span class="line"> 对db01 3307节点进行关闭和启动,测试读写操作</span><br></pre></td></tr></table></figure>

<h1 id="9-配置中的属性介绍"><a href="#9-配置中的属性介绍" class="headerlink" title="9. 配置中的属性介绍:"></a>9. 配置中的属性介绍:</h1><h2 id="balance属性"><a href="#balance属性" class="headerlink" title="balance属性"></a>balance属性</h2><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">负载均衡类型，目前的取值有<span class="number">3</span>种： </span><br><span class="line"><span class="number">1.</span> balance=<span class="string">"0"</span>, 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。 </span><br><span class="line"><span class="number">2.</span> balance=<span class="string">"1"</span>，全部的readHost与standby writeHost参与<span class="keyword">select</span>语句的负载均衡，简单的说，</span><br><span class="line">  当双主双从模式(M1-&gt;S1，M2-&gt;S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与<span class="keyword">select</span>语句的负载均衡。 </span><br><span class="line"><span class="number">3.</span> balance=<span class="string">"2"</span>，所有读操作都随机的在writeHost、readhost上分发。</span><br></pre></td></tr></table></figure>

<h2 id="writeType属性"><a href="#writeType属性" class="headerlink" title="writeType属性"></a>writeType属性</h2><figure class="highlight ruby"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">负载均衡类型，目前的取值有<span class="number">2</span>种： </span><br><span class="line"><span class="number">1</span>. writeType=<span class="string">"0"</span>, 所有写操作发送到配置的第一个writeHost，</span><br><span class="line">第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为主，切换记录在配置文件中<span class="symbol">:dnindex</span>.properties . </span><br><span class="line"><span class="number">2</span>. writeType=“<span class="number">1</span>”，所有写操作都随机的发送到配置的writeHost，但不推荐使用</span><br></pre></td></tr></table></figure>

<h2 id="switchType属性"><a href="#switchType属性" class="headerlink" title="switchType属性"></a>switchType属性</h2><figure class="highlight dart"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="number">-1</span> 表示不自动切换 </span><br><span class="line"><span class="number">1</span> 默认值，自动切换 </span><br><span class="line"><span class="number">2</span> 基于MySQL主从同步的状态决定是否切换 ，心跳语句为 <span class="keyword">show</span> slave status</span><br></pre></td></tr></table></figure>

<h2 id="datahost其他配置"><a href="#datahost其他配置" class="headerlink" title="datahost其他配置"></a>datahost其他配置</h2><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">&lt;dataHost name=<span class="string">"localhost1"</span> maxCon=<span class="string">"1000"</span> minCon=<span class="string">"10"</span> balance=<span class="string">"1"</span>  writeType=<span class="string">"0"</span> dbType=<span class="string">"mysql"</span>  dbDriver=<span class="string">"native"</span> switchType=<span class="string">"1"</span>&gt; </span><br><span class="line"></span><br><span class="line">maxCon=<span class="string">"1000"</span>：最大的并发连接数</span><br><span class="line">minCon=<span class="string">"10"</span> ：mycat在启动之后，会在后端节点上自动开启的连接线程</span><br><span class="line">tempReadHostAvailable=<span class="string">"1"</span></span><br><span class="line">这个一主一从时（<span class="number">1</span>个writehost，<span class="number">1</span>个readhost时），可以开启这个参数，如果<span class="number">2</span>个writehost，<span class="number">2</span>个readhost时</span><br><span class="line">&lt;heartbeat&gt;<span class="function"><span class="keyword">select</span> <span class="title">user</span>(<span class="params"></span>)&lt;/heartbeat&gt;  监测心跳</span></span><br></pre></td></tr></table></figure>

<h1 id="10-垂直分表"><a href="#10-垂直分表" class="headerlink" title="10. 垂直分表"></a>10. 垂直分表</h1><p><img src="https:////upload-images.jianshu.io/upload_images/16956686-c188becb93fdef0f.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1059/format/webp" alt="img"></p>
<p>image.png</p>
<p><img src="https:////upload-images.jianshu.io/upload_images/16956686-96987959032a94c2.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1063/format/webp" alt="img"></p>
<p>image.png</p>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br></pre></td><td class="code"><pre><span class="line">mv  schema.xml  schema.xml.ha </span><br><span class="line">vim schema.xml</span><br><span class="line"><span class="meta">&lt;?xml version="1.0"?&gt;</span></span><br><span class="line"><span class="meta">&lt;!DOCTYPE <span class="meta-keyword">mycat</span>:schema <span class="meta-keyword">SYSTEM</span> <span class="meta-string">"schema.dtd"</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;<span class="name">mycat:schema</span> <span class="attr">xmlns:mycat</span>=<span class="string">"http://io.mycat/"</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;<span class="name">schema</span> <span class="attr">name</span>=<span class="string">"TESTDB"</span> <span class="attr">checkSQLschema</span>=<span class="string">"false"</span> <span class="attr">sqlMaxLimit</span>=<span class="string">"100"</span> <span class="attr">dataNode</span>=<span class="string">"sh1"</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">table</span> <span class="attr">name</span>=<span class="string">"user"</span> <span class="attr">dataNode</span>=<span class="string">"sh1"</span>/&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">table</span> <span class="attr">name</span>=<span class="string">"order_t"</span> <span class="attr">dataNode</span>=<span class="string">"sh2"</span>/&gt;</span></span><br><span class="line"><span class="tag">&lt;/<span class="name">schema</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="name">dataNode</span> <span class="attr">name</span>=<span class="string">"sh1"</span> <span class="attr">dataHost</span>=<span class="string">"oldguo1"</span> <span class="attr">database</span>= <span class="string">"taobao"</span> /&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="name">dataNode</span> <span class="attr">name</span>=<span class="string">"sh2"</span> <span class="attr">dataHost</span>=<span class="string">"oldguo2"</span> <span class="attr">database</span>= <span class="string">"taobao"</span> /&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="name">dataHost</span> <span class="attr">name</span>=<span class="string">"oldguo1"</span> <span class="attr">maxCon</span>=<span class="string">"1000"</span> <span class="attr">minCon</span>=<span class="string">"10"</span> <span class="attr">balance</span>=<span class="string">"1"</span>  <span class="attr">writeType</span>=<span class="string">"0"</span> <span class="attr">dbType</span>=<span class="string">"mysql"</span>  <span class="attr">dbDriver</span>=<span class="string">"native"</span> <span class="attr">switchType</span>=<span class="string">"1"</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">heartbeat</span>&gt;</span>select user()<span class="tag">&lt;/<span class="name">heartbeat</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="name">writeHost</span> <span class="attr">host</span>=<span class="string">"db1"</span> <span class="attr">url</span>=<span class="string">"10.0.0.51:3307"</span> <span class="attr">user</span>=<span class="string">"root"</span> <span class="attr">password</span>=<span class="string">"123"</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">readHost</span> <span class="attr">host</span>=<span class="string">"db2"</span> <span class="attr">url</span>=<span class="string">"10.0.0.51:3309"</span> <span class="attr">user</span>=<span class="string">"root"</span> <span class="attr">password</span>=<span class="string">"123"</span> /&gt;</span></span><br><span class="line">    <span class="tag">&lt;/<span class="name">writeHost</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="name">writeHost</span> <span class="attr">host</span>=<span class="string">"db3"</span> <span class="attr">url</span>=<span class="string">"10.0.0.52:3307"</span> <span class="attr">user</span>=<span class="string">"root"</span> <span class="attr">password</span>=<span class="string">"123"</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">readHost</span> <span class="attr">host</span>=<span class="string">"db4"</span> <span class="attr">url</span>=<span class="string">"10.0.0.52:3309"</span> <span class="attr">user</span>=<span class="string">"root"</span> <span class="attr">password</span>=<span class="string">"123"</span> /&gt;</span></span><br><span class="line">    <span class="tag">&lt;/<span class="name">writeHost</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;/<span class="name">dataHost</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="name">dataHost</span> <span class="attr">name</span>=<span class="string">"oldguo2"</span> <span class="attr">maxCon</span>=<span class="string">"1000"</span> <span class="attr">minCon</span>=<span class="string">"10"</span> <span class="attr">balance</span>=<span class="string">"1"</span>  <span class="attr">writeType</span>=<span class="string">"0"</span> <span class="attr">dbType</span>=<span class="string">"mysql"</span>  <span class="attr">dbDriver</span>=<span class="string">"native"</span> <span class="attr">switchType</span>=<span class="string">"1"</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">heartbeat</span>&gt;</span>select user()<span class="tag">&lt;/<span class="name">heartbeat</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="name">writeHost</span> <span class="attr">host</span>=<span class="string">"db1"</span> <span class="attr">url</span>=<span class="string">"10.0.0.51:3308"</span> <span class="attr">user</span>=<span class="string">"root"</span> <span class="attr">password</span>=<span class="string">"123"</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">readHost</span> <span class="attr">host</span>=<span class="string">"db2"</span> <span class="attr">url</span>=<span class="string">"10.0.0.51:3310"</span> <span class="attr">user</span>=<span class="string">"root"</span> <span class="attr">password</span>=<span class="string">"123"</span> /&gt;</span></span><br><span class="line">    <span class="tag">&lt;/<span class="name">writeHost</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="name">writeHost</span> <span class="attr">host</span>=<span class="string">"db3"</span> <span class="attr">url</span>=<span class="string">"10.0.0.52:3308"</span> <span class="attr">user</span>=<span class="string">"root"</span> <span class="attr">password</span>=<span class="string">"123"</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">readHost</span> <span class="attr">host</span>=<span class="string">"db4"</span> <span class="attr">url</span>=<span class="string">"10.0.0.52:3310"</span> <span class="attr">user</span>=<span class="string">"root"</span> <span class="attr">password</span>=<span class="string">"123"</span> /&gt;</span></span><br><span class="line">    <span class="tag">&lt;/<span class="name">writeHost</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;/<span class="name">dataHost</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;/<span class="name">mycat:schema</span>&gt;</span></span><br><span class="line"></span><br><span class="line">创建测试库和表:</span><br><span class="line">[root@db01 conf]# mysql -S /data/3307/mysql.sock -e "create database taobao charset utf8;"</span><br><span class="line">[root@db01 conf]# mysql -S /data/3308/mysql.sock -e "create database taobao charset utf8;"</span><br><span class="line">[root@db01 conf]# mysql -S /data/3307/mysql.sock -e "use taobao;create table user(id int,name varchar(20))";</span><br><span class="line">[root@db01 conf]# mysql -S /data/3308/mysql.sock -e "use taobao;create table order_t(id int,name varchar(20))"</span><br></pre></td></tr></table></figure>

<h1 id="11-MyCAT核心特性——分片（水平拆分）"><a href="#11-MyCAT核心特性——分片（水平拆分）" class="headerlink" title="11. MyCAT核心特性——分片（水平拆分）"></a>11. MyCAT核心特性——分片（水平拆分）</h1><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line">分片：对一个<span class="string">"bigtable"</span>，比如说t3表</span><br><span class="line"></span><br><span class="line">(1)行数非常多，800w</span><br><span class="line">(2)访问非常频繁</span><br><span class="line"></span><br><span class="line">分片的目的：</span><br><span class="line">（1）将大数据量进行分布存储</span><br><span class="line">（2）提供均衡的访问路由</span><br><span class="line"></span><br><span class="line">分片策略：</span><br><span class="line">范围 range  800w  1-400w 400w01-800w</span><br><span class="line">取模 mod    取余数</span><br><span class="line">枚举 </span><br><span class="line">哈希 <span class="built_in">hash</span> </span><br><span class="line">时间 流水</span><br><span class="line"></span><br><span class="line">优化关联查询</span><br><span class="line">全局表</span><br><span class="line">ER分片</span><br></pre></td></tr></table></figure>

<h1 id="12-范围分片"><a href="#12-范围分片" class="headerlink" title="12 .范围分片"></a>12 .范围分片</h1><p><img src="https:////upload-images.jianshu.io/upload_images/16956686-71a4dc83c387d7b0.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/933/format/webp" alt="img"></p>
<p>image.png</p>
<figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br></pre></td><td class="code"><pre><span class="line">比如说t3表</span><br><span class="line">(<span class="number">1</span>)行数非常多，<span class="number">2000</span>w（<span class="number">1</span><span class="number">-1000</span>w:sh1   <span class="number">1000</span>w01<span class="number">-2000</span>w:sh2）</span><br><span class="line">(<span class="number">2</span>)访问非常频繁，用户访问较离散</span><br><span class="line">mv schema.xml schema.xml<span class="number">.1</span>  </span><br><span class="line">vim schema.xml</span><br><span class="line">&lt;schema name=<span class="string">"TESTDB"</span> checkSQLschema=<span class="string">"false"</span> sqlMaxLimit=<span class="string">"100"</span> dataNode=<span class="string">"sh1"</span>&gt; </span><br><span class="line">        &lt;table name=<span class="string">"t3"</span> dataNode=<span class="string">"sh1,sh2"</span> rule=<span class="string">"auto-sharding-long"</span> /&gt;</span><br><span class="line">&lt;/schema&gt;  </span><br><span class="line">    &lt;dataNode name=<span class="string">"sh1"</span> dataHost=<span class="string">"oldguo1"</span> database= <span class="string">"taobao"</span> /&gt; </span><br><span class="line">    &lt;dataNode name=<span class="string">"sh2"</span> dataHost=<span class="string">"oldguo2"</span> database= <span class="string">"taobao"</span> /&gt;  </span><br><span class="line"></span><br><span class="line">vim rule.xml</span><br><span class="line">&lt;tableRule name=<span class="string">"auto-sharding-long"</span>&gt;</span><br><span class="line">                &lt;rule&gt;</span><br><span class="line">                        &lt;columns&gt;id&lt;/columns&gt;</span><br><span class="line">                        &lt;algorithm&gt;rang-<span class="keyword">long</span>&lt;/algorithm&gt;</span><br><span class="line">                &lt;/rule&gt;             </span><br><span class="line">&lt;function name=<span class="string">"rang-long"</span></span><br><span class="line">    <span class="keyword">class</span>=<span class="string">"io.mycat.route.function.AutoPartitionByLong"</span>&gt;</span><br><span class="line">    &lt;property name=<span class="string">"mapFile"</span>&gt;autopartition-<span class="keyword">long</span>.txt&lt;/property&gt;</span><br><span class="line">&lt;/function&gt;</span><br><span class="line">===================================         </span><br><span class="line">vim autopartition-<span class="keyword">long</span>.txt</span><br><span class="line"><span class="number">0</span><span class="number">-10</span>=<span class="number">0</span></span><br><span class="line"><span class="number">11</span><span class="number">-20</span>=<span class="number">1</span></span><br><span class="line"></span><br><span class="line">创建测试表：</span><br><span class="line">mysql -S /data/<span class="number">3307</span>/mysql.sock -e <span class="string">"use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"</span></span><br><span class="line"></span><br><span class="line">mysql -S /data/<span class="number">3308</span>/mysql.sock  -e <span class="string">"use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"</span></span><br><span class="line"></span><br><span class="line">测试：</span><br><span class="line">重启mycat</span><br><span class="line">mycat restart</span><br><span class="line">mysql -uroot -p123456 -h <span class="number">127.0</span><span class="number">.0</span><span class="number">.1</span> -P <span class="number">8066</span></span><br><span class="line"><span class="function">insert <span class="keyword">into</span> <span class="title">t3</span>(<span class="params">id,name</span>) <span class="title">values</span>(<span class="params"><span class="number">1</span>,<span class="string">'a'</span></span>)</span>;</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> <span class="title">t3</span>(<span class="params">id,name</span>) <span class="title">values</span>(<span class="params"><span class="number">2</span>,<span class="string">'b'</span></span>)</span>;</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> <span class="title">t3</span>(<span class="params">id,name</span>) <span class="title">values</span>(<span class="params"><span class="number">3</span>,<span class="string">'c'</span></span>)</span>;</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> <span class="title">t3</span>(<span class="params">id,name</span>) <span class="title">values</span>(<span class="params"><span class="number">4</span>,<span class="string">'d'</span></span>)</span>;</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> <span class="title">t3</span>(<span class="params">id,name</span>) <span class="title">values</span>(<span class="params"><span class="number">11</span>,<span class="string">'aa'</span></span>)</span>;</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> <span class="title">t3</span>(<span class="params">id,name</span>) <span class="title">values</span>(<span class="params"><span class="number">12</span>,<span class="string">'bb'</span></span>)</span>;</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> <span class="title">t3</span>(<span class="params">id,name</span>) <span class="title">values</span>(<span class="params"><span class="number">13</span>,<span class="string">'cc'</span></span>)</span>;</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> <span class="title">t3</span>(<span class="params">id,name</span>) <span class="title">values</span>(<span class="params"><span class="number">14</span>,<span class="string">'dd'</span></span>)</span>;</span><br></pre></td></tr></table></figure>

<h1 id="13-取模分片（mod-long）："><a href="#13-取模分片（mod-long）：" class="headerlink" title="13. 取模分片（mod-long）："></a>13. 取模分片（mod-long）：</h1><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br></pre></td><td class="code"><pre><span class="line">取余分片方式：分片键（一个列）与节点数量进行取余，得到余数，将数据写入对应节点</span><br><span class="line">vim schema.xml</span><br><span class="line">&lt;table name=<span class="string">"t4"</span> dataNode=<span class="string">"sh1,sh2"</span> rule=<span class="string">"mod-long"</span> /&gt;</span><br><span class="line">vim rule.xml</span><br><span class="line">&lt;property name=<span class="string">"count"</span>&gt;<span class="number">2</span>&lt;/property&gt;</span><br><span class="line"></span><br><span class="line">准备测试环境</span><br><span class="line">     </span><br><span class="line">创建测试表：</span><br><span class="line">mysql -S /data/<span class="number">3307</span>/mysql.sock -e <span class="string">"use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"</span></span><br><span class="line">mysql -S /data/<span class="number">3308</span>/mysql.sock -e <span class="string">"use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"</span></span><br><span class="line"></span><br><span class="line">重启mycat </span><br><span class="line">mycat restart </span><br><span class="line"></span><br><span class="line">测试： </span><br><span class="line">mysql -uroot -p123456 -h10<span class="number">.0</span><span class="number">.0</span><span class="number">.52</span> -P8066</span><br><span class="line"></span><br><span class="line">use TESTDB</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> <span class="title">t4</span>(<span class="params">id,name</span>) <span class="title">values</span>(<span class="params"><span class="number">1</span>,<span class="string">'a'</span></span>)</span>;</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> <span class="title">t4</span>(<span class="params">id,name</span>) <span class="title">values</span>(<span class="params"><span class="number">2</span>,<span class="string">'b'</span></span>)</span>;</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> <span class="title">t4</span>(<span class="params">id,name</span>) <span class="title">values</span>(<span class="params"><span class="number">3</span>,<span class="string">'c'</span></span>)</span>;</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> <span class="title">t4</span>(<span class="params">id,name</span>) <span class="title">values</span>(<span class="params"><span class="number">4</span>,<span class="string">'d'</span></span>)</span>;</span><br><span class="line"></span><br><span class="line">分别登录后端节点查询数据</span><br><span class="line">mysql -S /data/<span class="number">3307</span>/mysql.sock </span><br><span class="line">use taobao</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> t4;</span><br><span class="line"></span><br><span class="line">mysql -S /data/<span class="number">3308</span>/mysql.sock </span><br><span class="line">use taobao</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> t4;</span><br></pre></td></tr></table></figure>

<h1 id="14-枚举分片"><a href="#14-枚举分片" class="headerlink" title="14. 枚举分片"></a>14. 枚举分片</h1><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br></pre></td><td class="code"><pre><span class="line">t5 表</span><br><span class="line">id name telnum</span><br><span class="line"><span class="number">1</span>   bj   <span class="number">1212</span></span><br><span class="line"><span class="number">2</span>   sh   <span class="number">22222</span></span><br><span class="line"><span class="number">3</span>   bj   <span class="number">3333</span></span><br><span class="line"><span class="number">4</span>   sh   <span class="number">44444</span></span><br><span class="line"><span class="number">5</span>   bj   <span class="number">5555</span></span><br><span class="line"></span><br><span class="line">sharding-<span class="keyword">by</span>-intfile</span><br><span class="line">vim schema.xml</span><br><span class="line">&lt;table name=<span class="string">"t5"</span> dataNode=<span class="string">"sh1,sh2"</span> rule=<span class="string">"sharding-by-intfile"</span> /&gt;</span><br><span class="line"></span><br><span class="line">vim rule.xml</span><br><span class="line">&lt;tableRule name=<span class="string">"sharding-by-intfile"</span>&gt; </span><br><span class="line">&lt;rule&gt; &lt;columns&gt;name&lt;/columns&gt; </span><br><span class="line">&lt;algorithm&gt;hash-<span class="keyword">int</span>&lt;/algorithm&gt; </span><br><span class="line">&lt;/rule&gt; </span><br><span class="line">&lt;/tableRule&gt; </span><br><span class="line"></span><br><span class="line">&lt;function name=<span class="string">"hash-int"</span> <span class="keyword">class</span>=<span class="string">"org.opencloudb.route.function.PartitionByFileMap"</span>&gt; </span><br><span class="line">&lt;property name=<span class="string">"mapFile"</span>&gt;partition-hash-<span class="keyword">int</span>.txt&lt;/property&gt; </span><br><span class="line">  &lt;property name=<span class="string">"type"</span>&gt;<span class="number">1</span>&lt;/property&gt;</span><br><span class="line">                &lt;property name=<span class="string">"defaultNode"</span>&gt;<span class="number">0</span>&lt;/property&gt;</span><br><span class="line">&lt;/function&gt; </span><br><span class="line"></span><br><span class="line">partition-hash-<span class="keyword">int</span>.txt 配置： </span><br><span class="line">bj=<span class="number">0</span> </span><br><span class="line">sh=<span class="number">1</span></span><br><span class="line">DEFAULT_NODE=<span class="number">1</span> </span><br><span class="line">columns 标识将要分片的表字段，algorithm 分片函数， 其中分片函数配置中，mapFile标识配置文件名称</span><br><span class="line"></span><br><span class="line">准备测试环境</span><br><span class="line">mysql -S /data/<span class="number">3307</span>/mysql.sock -e <span class="string">"use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"</span></span><br><span class="line"></span><br><span class="line">mysql -S /data/<span class="number">3308</span>/mysql.sock -e <span class="string">"use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"</span></span><br><span class="line">重启mycat </span><br><span class="line">mycat restart </span><br><span class="line">mysql -uroot -p123456 -h10<span class="number">.0</span><span class="number">.0</span><span class="number">.51</span> -P8066</span><br><span class="line">use TESTDB</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> <span class="title">t5</span>(<span class="params">id,name</span>) <span class="title">values</span>(<span class="params"><span class="number">1</span>,<span class="string">'bj'</span></span>)</span>;</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> <span class="title">t5</span>(<span class="params">id,name</span>) <span class="title">values</span>(<span class="params"><span class="number">2</span>,<span class="string">'sh'</span></span>)</span>;</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> <span class="title">t5</span>(<span class="params">id,name</span>) <span class="title">values</span>(<span class="params"><span class="number">3</span>,<span class="string">'bj'</span></span>)</span>;</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> <span class="title">t5</span>(<span class="params">id,name</span>) <span class="title">values</span>(<span class="params"><span class="number">4</span>,<span class="string">'sh'</span></span>)</span>;</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> <span class="title">t5</span>(<span class="params">id,name</span>) <span class="title">values</span>(<span class="params"><span class="number">5</span>,<span class="string">'tj'</span></span>)</span>;</span><br></pre></td></tr></table></figure>

<h1 id="15-Mycat全局表"><a href="#15-Mycat全局表" class="headerlink" title="15 .  Mycat全局表"></a>15 .  Mycat全局表</h1><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br></pre></td><td class="code"><pre><span class="line">a   b   c  d   </span><br><span class="line"><span class="keyword">join</span> </span><br><span class="line">t </span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span>  t1.name   ,t.x  <span class="keyword">from</span>  t1 </span><br><span class="line"><span class="keyword">join</span> t </span><br><span class="line"><span class="keyword">select</span>  t2.name   ,t.x  <span class="keyword">from</span>  t2 </span><br><span class="line"><span class="keyword">join</span> t </span><br><span class="line"><span class="keyword">select</span>  t3.name   ,t.x  <span class="keyword">from</span>  t3 </span><br><span class="line"><span class="keyword">join</span> t </span><br><span class="line"></span><br><span class="line">使用场景：</span><br><span class="line">如果你的业务中有些数据类似于数据字典，比如配置文件的配置，</span><br><span class="line">常用业务的配置或者数据量不大很少变动的表，这些表往往不是特别大，</span><br><span class="line">而且大部分的业务场景都会用到，那么这种表适合于Mycat全局表，无须对数据进行切分，</span><br><span class="line">要在所有的分片上保存一份数据即可，Mycat 在Join操作中，业务表与全局表进行Join聚合会优先选择相同分片内的全局表<span class="keyword">join</span>，</span><br><span class="line">避免跨库Join，在进行数据插入操作时，mycat将把数据分发到全局表对应的所有分片执行，在进行数据读取时候将会随机获取一个节点读取数据。 </span><br><span class="line"></span><br><span class="line">vim schema.xml </span><br><span class="line">&lt;table name=<span class="string">"t_area"</span> primaryKey=<span class="string">"id"</span>  type=<span class="string">"global"</span> dataNode=<span class="string">"sh1,sh2"</span> /&gt; </span><br><span class="line"></span><br><span class="line">后端数据准备</span><br><span class="line">mysql -S /data/<span class="number">3307</span>/mysql.sock </span><br><span class="line">use taobao</span><br><span class="line"><span class="function">create table <span class="title">t_area</span> (<span class="params">id <span class="keyword">int</span> not <span class="literal">null</span> primary key auto_increment,name varchar(<span class="number">20</span></span>) not <span class="literal">null</span>)</span>;</span><br><span class="line"></span><br><span class="line">mysql -S /data/<span class="number">3308</span>/mysql.sock </span><br><span class="line">use taobao</span><br><span class="line"><span class="function">create table <span class="title">t_area</span>  (<span class="params">id <span class="keyword">int</span> not <span class="literal">null</span> primary key auto_increment,name varchar(<span class="number">20</span></span>) not <span class="literal">null</span>)</span>;</span><br><span class="line"></span><br><span class="line">重启mycat </span><br><span class="line">mycat restart </span><br><span class="line"></span><br><span class="line">测试： </span><br><span class="line">mysql -uroot -p123456 -h10<span class="number">.0</span><span class="number">.0</span><span class="number">.52</span> -P8066</span><br><span class="line"></span><br><span class="line">use TESTDB</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> <span class="title">t_area</span>(<span class="params">id,name</span>) <span class="title">values</span>(<span class="params"><span class="number">1</span>,<span class="string">'a'</span></span>)</span>;</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> <span class="title">t_area</span>(<span class="params">id,name</span>) <span class="title">values</span>(<span class="params"><span class="number">2</span>,<span class="string">'b'</span></span>)</span>;</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> <span class="title">t_area</span>(<span class="params">id,name</span>) <span class="title">values</span>(<span class="params"><span class="number">3</span>,<span class="string">'c'</span></span>)</span>;</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> <span class="title">t_area</span>(<span class="params">id,name</span>) <span class="title">values</span>(<span class="params"><span class="number">4</span>,<span class="string">'d'</span></span>)</span>;</span><br></pre></td></tr></table></figure>

<h1 id="16-E-R分片"><a href="#16-E-R分片" class="headerlink" title="16. E-R分片"></a>16. E-R分片</h1><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">A </span><br><span class="line"><span class="keyword">join</span> </span><br><span class="line">B  </span><br><span class="line">为了防止跨分片<span class="keyword">join</span>，可以使用E-R模式</span><br><span class="line">A   <span class="keyword">join</span>   B</span><br><span class="line"><span class="keyword">on</span>  a.xx=b.yy</span><br><span class="line"><span class="keyword">join</span> C</span><br><span class="line"><span class="keyword">on</span> A.id=C.id</span><br><span class="line">&lt;table name=<span class="string">"A"</span> dataNode=<span class="string">"sh1,sh2"</span> rule=<span class="string">"mod-long"</span>&gt; </span><br><span class="line">       &lt;childTable name=<span class="string">"B"</span> joinKey=<span class="string">"yy"</span> parentKey=<span class="string">"xx"</span> /&gt; </span><br><span class="line">&lt;/table&gt;</span><br></pre></td></tr></table></figure>



<p>作者：wwwoldguocom<br>链接：<a href="https://www.jianshu.com/p/5e0062f6cf62" target="_blank" rel="noopener">https://www.jianshu.com/p/5e0062f6cf62</a><br>来源：简书<br>著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。</p>
</section>
    <!-- Tags START -->
    
    <!-- Tags END -->
    <!-- NAV START -->
    
  <div class="nav-container">
    <!-- reverse left and right to put prev and next in a more logic postition -->
    
      <a class="nav-left" href="/db/MySQL%E7%B3%BB%E5%88%97/06-MySQL%E7%B3%BB%E5%88%97%E4%B9%8B-%E6%97%A5%E5%BF%97%E7%AE%A1%E7%90%86/">
        <span class="nav-arrow">← </span>
        
          db/MySQL系列/06-MySQL系列之-日志管理
        
      </a>
    
    
      <a class="nav-right" href="/db/MySQL%E7%B3%BB%E5%88%97/09-MySQL%E7%B3%BB%E5%88%97%E4%B9%8B-%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6%E8%BF%9B%E9%98%B6/">
        
          db/MySQL系列/09-MySQL系列之-主从复制进阶
        
        <span class="nav-arrow"> →</span>
      </a>
    
  </div>

    <!-- NAV END -->
    <!-- 打赏 START -->
    
      <div class="money-like">
        <div class="reward-btn">
          赏
          <span class="money-code">
            <span class="alipay-code">
              <div class="code-image"></div>
              <b>使用支付宝打赏</b>
            </span>
            <span class="wechat-code">
              <div class="code-image"></div>
              <b>使用微信打赏</b>
            </span>
          </span>
        </div>
        <p class="notice">点击上方按钮,请我喝杯咖啡！</p>
      </div>
    
    <!-- 打赏 END -->
    <!-- 二维码 START -->
    
      <div class="qrcode">
        <canvas id="share-qrcode"></canvas>
        <p class="notice">扫描二维码，分享此文章</p>
      </div>
    
    <!-- 二维码 END -->
    
      <!-- Gitment START -->
      <div id="comments"></div>
      <!-- Gitment END -->
    
  </article>
  <!-- Article END -->
  <!-- Catalog START -->
  
    <aside class="catalog-container">
  <div class="toc-main">
  <!-- 不蒜子统计 -->
    <strong class="toc-title">目录</strong>
    
      <ol class="toc-nav"><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#1-MyCAT基础架构图"><span class="toc-nav-text">1. MyCAT基础架构图</span></a></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#2-MyCAT基础架构准备"><span class="toc-nav-text">2. MyCAT基础架构准备</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-1-环境准备："><span class="toc-nav-text">2.1 环境准备：</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-2-删除历史环境："><span class="toc-nav-text">2.2 删除历史环境：</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-3-创建相关目录初始化数据"><span class="toc-nav-text">2.3 创建相关目录初始化数据</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-4-准备配置文件和启动脚本"><span class="toc-nav-text">2.4 准备配置文件和启动脚本</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-5-修改权限，启动多实例"><span class="toc-nav-text">2.5 修改权限，启动多实例</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-6-节点主从规划"><span class="toc-nav-text">2.6 节点主从规划</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-7-分片规划"><span class="toc-nav-text">2.7 分片规划</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-8-开始配置"><span class="toc-nav-text">2.8 开始配置</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#shard1"><span class="toc-nav-text">shard1</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#10-0-0-51-3307-lt-—–-gt-10-0-0-52-3307"><span class="toc-nav-text">10.0.0.51:3307    &lt;—–&gt;  10.0.0.52:3307</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#db02"><span class="toc-nav-text">db02</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#db01"><span class="toc-nav-text">db01</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#db02-1"><span class="toc-nav-text">db02</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#10-0-0-51-3309-——-gt-10-0-0-51-3307"><span class="toc-nav-text">10.0.0.51:3309    ——&gt;  10.0.0.51:3307</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#db01-1"><span class="toc-nav-text">db01</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#10-0-0-52-3309-——-gt-10-0-0-52-3307"><span class="toc-nav-text">10.0.0.52:3309    ——&gt;  10.0.0.52:3307</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#db02-2"><span class="toc-nav-text">db02</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#shard2"><span class="toc-nav-text">shard2</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#10-0-0-52-3308-lt-—–-gt-10-0-0-51-3308"><span class="toc-nav-text">10.0.0.52:3308  &lt;—–&gt;    10.0.0.51:3308</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#db01-2"><span class="toc-nav-text">db01</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#db02-3"><span class="toc-nav-text">db02</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#db01-3"><span class="toc-nav-text">db01</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#10-0-0-52-3310-—–-gt-10-0-0-52-3308"><span class="toc-nav-text">10.0.0.52:3310    —–&gt;       10.0.0.52:3308</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#db02-4"><span class="toc-nav-text">db02</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#10-0-0-51-3310-—–-gt-10-0-0-51-3308"><span class="toc-nav-text">10.0.0.51:3310  —–&gt;     10.0.0.51:3308</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#db01-4"><span class="toc-nav-text">db01</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-9-检测主从状态"><span class="toc-nav-text">2.9 检测主从状态</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-10-MySQL分布式架构介绍"><span class="toc-nav-text">2.10 MySQL分布式架构介绍</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-11-企业代表产品"><span class="toc-nav-text">2.11 企业代表产品</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#3-MyCAT安装"><span class="toc-nav-text">3. MyCAT安装</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-1-预先安装Java运行环境"><span class="toc-nav-text">3.1 预先安装Java运行环境</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-2下载"><span class="toc-nav-text">3.2下载</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-3-解压文件"><span class="toc-nav-text">3.3 解压文件</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-4-软件目录结构"><span class="toc-nav-text">3.4 软件目录结构</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-5-启动和连接"><span class="toc-nav-text">3.5 启动和连接</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#4-配置文件介绍"><span class="toc-nav-text">4. 配置文件介绍</span></a></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#5-应用前环境准备"><span class="toc-nav-text">5.应用前环境准备</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#5-1-用户创建及数据库导入"><span class="toc-nav-text">5.1 用户创建及数据库导入</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#5-2-配置文件处理"><span class="toc-nav-text">5.2 配置文件处理</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#6-配置文件简单介绍"><span class="toc-nav-text">6. 配置文件简单介绍</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#6-1-逻辑库：schema"><span class="toc-nav-text">6.1 逻辑库：schema</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#6-2-数据节点-datanode"><span class="toc-nav-text">6.2 数据节点:datanode</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#6-3-数据主机：datahost-w和r"><span class="toc-nav-text">6.3 数据主机：datahost(w和r)</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#7-读写分离结构配置"><span class="toc-nav-text">7. 读写分离结构配置</span></a></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#8-配置读写分离及高可用"><span class="toc-nav-text">8. 配置读写分离及高可用</span></a></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#9-配置中的属性介绍"><span class="toc-nav-text">9. 配置中的属性介绍:</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#balance属性"><span class="toc-nav-text">balance属性</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#writeType属性"><span class="toc-nav-text">writeType属性</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#switchType属性"><span class="toc-nav-text">switchType属性</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#datahost其他配置"><span class="toc-nav-text">datahost其他配置</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#10-垂直分表"><span class="toc-nav-text">10. 垂直分表</span></a></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#11-MyCAT核心特性——分片（水平拆分）"><span class="toc-nav-text">11. MyCAT核心特性——分片（水平拆分）</span></a></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#12-范围分片"><span class="toc-nav-text">12 .范围分片</span></a></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#13-取模分片（mod-long）："><span class="toc-nav-text">13. 取模分片（mod-long）：</span></a></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#14-枚举分片"><span class="toc-nav-text">14. 枚举分片</span></a></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#15-Mycat全局表"><span class="toc-nav-text">15 .  Mycat全局表</span></a></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#16-E-R分片"><span class="toc-nav-text">16. E-R分片</span></a></li></ol>
    
  </div>
</aside>
  
  <!-- Catalog END -->
</main>

<script>
  (function () {
    var url = 'http://www.liuqingzheng.top/db/MySQL系列/12-MySQL系列之-MyCat/';
    var banner = ''
    if (banner !== '' && banner !== 'undefined' && banner !== 'null') {
      $('#article-banner').css({
        'background-image': 'url(' + banner + ')'
      })
    } else {
      $('#article-banner').geopattern(url)
    }
    $('.header').removeClass('fixed-header')

    // error image
    $(".markdown-content img").on('error', function() {
      $(this).attr('src', 'http://file.muyutech.com/error-img.png')
      $(this).css({
        'cursor': 'default'
      })
    })

    // zoom image
    $(".markdown-content img").on('click', function() {
      var src = $(this).attr('src')
      if (src !== 'http://file.muyutech.com/error-img.png') {
        var imageW = $(this).width()
        var imageH = $(this).height()

        var zoom = ($(window).width() * 0.95 / imageW).toFixed(2)
        zoom = zoom < 1 ? 1 : zoom
        zoom = zoom > 2 ? 2 : zoom
        var transY = (($(window).height() - imageH) / 2).toFixed(2)

        $('body').append('<div class="image-view-wrap"><div class="image-view-inner"><img src="'+ src +'" /></div></div>')
        $('.image-view-wrap').addClass('wrap-active')
        $('.image-view-wrap img').css({
          'width': `${imageW}`,
          'transform': `translate3d(0, ${transY}px, 0) scale3d(${zoom}, ${zoom}, 1)`
        })
        $('html').css('overflow', 'hidden')

        $('.image-view-wrap').on('click', function() {
          $(this).remove()
          $('html').attr('style', '')
        })
      }
    })
  })();
</script>


  <script>
    var qr = new QRious({
      element: document.getElementById('share-qrcode'),
      value: document.location.href
    });
  </script>



  <script>
    var gitmentConfig = "liuqingzheng";
    if (gitmentConfig !== 'undefined') {
      var gitment = new Gitment({
        id: "db/MySQL系列/12-MySQL系列之-MyCat",
        owner: "liuqingzheng",
        repo: "FuckBlog",
        oauth: {
          client_id: "32a4076431cf39d0ecea",
          client_secret: "94484bd79b3346a949acb2fda3c8a76ce16990c6"
        },
        theme: {
          render(state, instance) {
            const container = document.createElement('div')
            container.lang = "en-US"
            container.className = 'gitment-container gitment-root-container'
            container.appendChild(instance.renderHeader(state, instance))
            container.appendChild(instance.renderEditor(state, instance))
            container.appendChild(instance.renderComments(state, instance))
            container.appendChild(instance.renderFooter(state, instance))
            return container;
          }
        }
      })
      gitment.render(document.getElementById('comments'))
    }
  </script>




    <div class="scroll-top">
  <span class="arrow-icon"></span>
</div>
    <footer class="app-footer">
<!-- 不蒜子统计 -->
<span id="busuanzi_container_site_pv">
     本站总访问量<span id="busuanzi_value_site_pv"></span>次
</span>
<span class="post-meta-divider">|</span>
<span id="busuanzi_container_site_uv" style='display:none'>
     本站访客数<span id="busuanzi_value_site_uv"></span>人
</span>
<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>



  <p class="copyright">
    &copy; 2021 | Proudly powered by <a href="https://www.cnblogs.com/xiaoyuanqujing" target="_blank">小猿取经</a>
    <br>
    Theme by <a href="https://www.cnblogs.com/xiaoyuanqujing" target="_blank" rel="noopener">小猿取经</a>
  </p>
</footer>

<script>
  function async(u, c) {
    var d = document, t = 'script',
      o = d.createElement(t),
      s = d.getElementsByTagName(t)[0];
    o.src = u;
    if (c) { o.addEventListener('load', function (e) { c(null, e); }, false); }
    s.parentNode.insertBefore(o, s);
  }
</script>
<script>
  async("//cdnjs.cloudflare.com/ajax/libs/fastclick/1.0.6/fastclick.min.js", function(){
    FastClick.attach(document.body);
  })
</script>

<script>
  var hasLine = 'true';
  async("//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/highlight.min.js", function(){
    $('figure pre').each(function(i, block) {
      var figure = $(this).parents('figure');
      if (hasLine === 'false') {
        figure.find('.gutter').hide();
      }
      var lang = figure.attr('class').split(' ')[1] || 'code';
      var codeHtml = $(this).html();
      var codeTag = document.createElement('code');
      codeTag.className = lang;
      codeTag.innerHTML = codeHtml;
      $(this).attr('class', '').empty().html(codeTag);
      figure.attr('data-lang', lang.toUpperCase());
      hljs.highlightBlock(block);
    });
  })
</script>





<!-- Baidu Tongji -->

<script>
    var _baId = 'c5fd96eee1193585be191f318c3fa725';
    // Originial
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "//hm.baidu.com/hm.js?" + _baId;
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
</script>


<script src="/js/script.js"></script>


<script src="/js/search.js"></script>


<script src="/js/load.js"></script>



  <span class="local-search local-search-google local-search-plugin" style="right: 50px;top: 70px;;position:absolute;z-index:2;">
      <input type="search" placeholder="站内搜索" id="local-search-input" class="local-search-input-cls" style="">
      <div id="local-search-result" class="local-search-result-cls"></div>
  </span>


  </body>
</html>